Oracle SQL update based on a subquery between two tables

Question

I am currently writing update instructions to continually update a query-oriented table. The schema is identical between both tables, and the content is not important:

STAGING ID NAME COUNT PRODUCTION ID NAME COUNT 

My update statement is as follows:

 update PRODUCTION set name = (select stage.name from staging stage where stage.name=name and rownum <2), count = (select stage.countfrom staging stage where stage.count=count and rownum <2); 

It should be noted that 1) at the end of my update there is no where clause (this may be a problem) and 2) all records after the update have the same values. I mean the following:

 BEFORE UPDATE: 1,"JOHN", 12; 2,"STEVE",15; 3,"BETTY",2; AFTER UPDATE 1,"JOHN", 12; 2,"JOHN",12; 3,"JOHN",12; 

My question is how to fix this so that the table correctly reflects the “new” data from the stage as the correct SQL update?

UPDATE

So, my production data may coincide with what is in PRODUCTION , and for discussion it will be:

 STAGING DATA TO MERGE: 1,"JOHN", 12; 2,"STEVE",15; 3,"BETTY",2; 

UPDATE second

The query I would like to run would be as follows:

 update PRODUCTION set production.name = staging.name, production.count = staging.count where production.name = staging.name; 

This leads to invalid id problems on "staging.name"

Answer 1

There are two ways to do what you are trying.

One is a multi-column correlated update

 UPDATE PRODUCTION a SET (name, count) = ( SELECT name, count FROM STAGING b WHERE a.ID = b.ID); 

Demo

You can use merge

 MERGE INTO PRODUCTION a USING ( select id, name, count from STAGING ) b ON ( a.id = b.id ) WHEN MATCHED THEN UPDATE SET a.name = b.name, a.count = b.count 

Demo

Answer 2

Without examples of a production dataset, this is a shot in the dark, but have you tried something like this?

 update PRODUCTION p, staging s set p.name = s.name p.count = s.count where p.id = s.id 

This will work if the identifier column matches both tables.

Answer 3

As you noticed, you do not have selectivity for your update statement, so it updates your entire table. If you want to update certain rows (that is, where the identifiers match), you probably want to execute a coordinated subquery.

However, since you are using Oracle, it may be easier to create a materialized view for your query table and let the Oracle transaction engine handle the details. MVs work just like a table for querying semantics, they are pretty easy to configure, and you can specify an update interval.

Answer 4

Give it a try.
PRODUCT UPDATE a
SET (name, account) = (
SELECT name, count
FROM STAGING b
WHERE a.ID = b.ID)
WHERE EXIST (CHOICE 1
FROM STAGING b
WHERE a.ID = b.ID
);