CODEWITHSUNDEEP
sqloracle-database
Woot4Moo
Woot4Moo

Reputation: 24336

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"

Upvotes: 12

Views: 163219

Answers (4)

Obaidul
Obaidul

Reputation: 57

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

Upvotes: 3

Conrad Frix
Conrad Frix

Reputation: 52675

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

Upvotes: 40

Hermit
Hermit

Reputation: 387

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.

Upvotes: 2

khoxsey
khoxsey

Reputation: 1403

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.

Upvotes: 1

Related Questions