?
Solved

Insert and update a row at the same time

Posted on 2016-11-08
4
Medium Priority
?
96 Views
Last Modified: 2016-11-09
I am doing this with Oracle database tables:

I want to insert a row and update the row which was inserted at the same time with a date value.

For example I am comparing two tables and getting a result to a third table.

I am using an insert statement as follows:
insert into table3 (select * from table1 A where not exists (select row1 from table2 B where A.row1=B.row1));

All three tables have same columns. They all have a date column.

Whatever result was coming from the table1 should be updated with todays date in table 3.

The insert statement inserts the value into Table3 but I want to update the date with today's value simultaneously.

Is there a way, please let me know? Thank You for all your help!!

Table 1:

S.No            Date          Name         Phone
1                  11-2-13       abc             123
2                   11-4-12      xyz                456
3                   3-1-11         ghj              498

Table 2:

S.No            Date          Name         Phone
1                  11-2-13       abc             123
2                   11-4-12      xyz                456

result after insert and update on table 3

Table 3:

3                   11-8-2016        ghj              498
0
Comment
Question by:DevSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41879317
Don't use * in the select and use the sysdate function to return today's date with time

insert into table3 (select  S_No,  sysdate, Name, Phone from table1 A where not exists (select row1 from table2 B where A.row1=B.row1));

If you want the time portion set to zero's, use trunc(sysdate).
0
 

Author Comment

by:DevSupport
ID: 41879388
The select statement works but when I try to insert it says too many values:

SQL Error: ORA-00913: too many values
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 41879394
I don't have your table names or descrpitions so I cannot post an exact answer.

You need to match up the number of columns in the table with the number of columns in the select.

If the table has 3 columns, the select needs 3 columns, etc...

It is also a good idea to specify the column names in the insert side as well:
insert into table3(s_no,some_date,name,phone)
    (select  S_No,  sysdate, Name, Phone from table1 A where not exists (select row1 from table2 B where A.row1=B.row1));
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question