Link to home
Start Free TrialLog in
Avatar of DevSupport
DevSupport

asked on

Insert and update a row at the same time

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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).
Avatar of DevSupport

ASKER

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

SQL Error: ORA-00913: too many values
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial