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
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
ASKER
The select statement works but when I try to insert it says too many values:
SQL Error: ORA-00913: too many values
SQL Error: ORA-00913: too many values
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).