CODEWITHSUNDEEP
sqloracle-databaseoracle11g
randomizertech
randomizertech

Reputation: 2329

SQL - Update table values from values in another table

I have a select statement that is showing me all the data from table original every time it does not match the values on table real_values.

So every time it does not match, instead of showing me which routes have the wrong values for capacity, I would like the query to update it with the correct values.

Here is a shorter version to use as an example: http://sqlfiddle.com/#!4/6a904/1

Instead of being a select statement, how could I just update the values? I have tried some things I've seen online but nothing seems to work.

Upvotes: 1

Views: 61

Answers (3)

Allan
Allan

Reputation: 17429

@DavidFaber's answer is how most people would do this. However, for this kind of query, I prefer to use merge over update:

MERGE INTO original o
USING      real_values rv
ON         (o.origin = rv.origin AND o.destination = rv.destination)
WHEN MATCHED THEN
   UPDATE SET
      o.capacity_wt = rv.capacity_wt, o.capacity_vol = rv.capacity_vol
      WHERE         o.capacity_wt != rv.capacity_wt
                 OR o.capacity_vol != rv.capacity_vol

(It was unclear to me from your question whether you want to update original or real_values, so I chose one. If I got this wrong, reversing it should be trivial.)

I find merge more readable and easier to use when you want to update multiple columns.

Upvotes: 1

Nicolas M
Nicolas M

Reputation: 81

In SQL Server, you would do this

update original set capacity_wt=b.capacity_wt,capacity_vol=b.capacity_vol
from original a, real_values b
where a.origin = b.origin 
and a.destination = b.destination 
and (a.capacity_wt != b.capacity_wt
or b.capacity_vol != b.capacity_vol);

Upvotes: 0

David Faber
David Faber

Reputation: 12486

The usual form of such an update query in Oracle is the following:

UPDATE table1 t1
   SET t1.value = ( SELECT t2.value FROM table2 t2
                     WHERE t2.key = t1.key )
 WHERE EXISTS ( SELECT 1 FROM table2 t2
                 WHERE t2.key = t1.key );

I'm confused though. You've tagged this question oracle and sql-server but your fiddle link uses MySQL.

Upvotes: 1

Related Questions