Oracle SQL update based on subquery between two tables

Question

I am currently writing update statements to keep a query-able table constantly up to date   The schema is identical between both tables and the contents are not important     STAGING    ID   NAME   COUNT      PRODUCTION    ID   NAME   COUNT   My update statement looks as follows     update PRODUCTION   set name    select stage name from staging stage where stage name name  and rownum  lt 2         count      select stage countfrom staging stage where stage count count  and rownum  lt 2     The two things of note is that 1   There is no where clause at the end of my update  this may be the problem  and 2  all records after being updated have the same values   What I mean by this is 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 do I fix this so that the table properly reflects  new  data from staging as a correct SQL update   UPDATE    So my staging data could coincidentally mirror what is in PRODUCTION and for the sake of discussion it will     STAGING DATA TO MERGE         1  JOHN   12        2  STEVE  15        3  BETTY  2     UPDATE the second    The query that I would like to run would be this     update PRODUCTION set production name   staging name        production count   staging count  where production name   staging name    This however results in invalid identifier issues on  staging name

User · Accepted Answer

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

User · Answer

Without examples of the dataset of staging 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 would work assuming the id column matches on both tables

User · Answer

Try it      UPDATE PRODUCTION a SET  name  count      SELECT name  count         FROM STAGING b         WHERE a ID   b ID  WHERE EXISTS  SELECT 1     FROM STAGING b     WHERE a ID b ID

User · Answer

As you ve noticed  you have no selectivity to your update statement so it is updating your entire table  If you want to update specific rows  ie where the IDs match  you probably want to do a coordinated subquery   However  since you are using Oracle  it might be easier to create a materialized view for your query table and let Oracle s transaction mechanism handle the details  MVs work exactly like a table for querying semantics  are quite easy to set up  and allow you to specify the refresh interval

[sql] Oracle SQL update based on subquery between two tables

Examples related to sql

Examples related to oracle