[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

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
0
DevSupport
Asked:
DevSupport
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
DevSupportAuthor Commented:
The select statement works but when I try to insert it says too many values:

SQL Error: ORA-00913: too many values
0
 
slightwv (䄆 Netminder) Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now