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

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 <2),  
    count =   (select stage.countfrom staging stage where stage.count=count  and rownum <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"

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.

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.

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
 );