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
Solved

Insert and update a row at the same time

Posted on 2016-11-08
4
67 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
Query Records that don't match 8 50
grant user/role question 11 32
what privileges needed for S2 for this function (Oracle 12c)? 3 22
Oracle encryption 12 48
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

790 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