Oracle SQL update based on subquery between two tables

Viewed 145066

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"

4 Answers
Related