Solved

Insert and update a row at the same time

Posted on 2016-11-08
4
49 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
  • 2
4 Comments
 
LVL 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
levels for reporting 5 63
Wrap Oraccle SQL*Plus executable Command 4 66
Get the parent node - XMLTYPE 9 56
database upgrade 8 38
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now