selective update master table from another table

puzzzled · #1 (**permalink**) 11-30-04, 14:05

Hi, I'm new here...
I normally use a procedural language to do this, but I'd like to know if (and how) SQL can do the same.

Master table has something like:
Key Value
1 100
2 200
3 300
4 400

Transaction has something like:
Key NewValue
2 22
4 44
(those are simplified - real tables have a lot more data)

When I apply the transactions I want to change the master Value column to be whatever is in NewValue but *only* if the keys match - otherwise I don't want the value to change at all.

I've tried writing update with a sub-query to the transactions, but it overwrites all the non-matching rows with nulls!
For example:
update master M
set value = (select newvalue
from trans T
where M.key = T.key)

No SQL tutorial I can find seems to provide any such example - am I asking the impossible?

Pat Phelan · #2 (**permalink**) 11-30-04, 14:18

Standard SQL does not support join operations in an UPDATE statement, although many of the products add this ability as a "superset" feature (above and beyond standard SQL).

Although I'd probably be a lightweight and use the non-standard extension, I could do this using standard SQL like:

Code:

UPDATE master
   SET value = (SELECT newvalue
      FROM trans AS T
      WHERE T.key = master.key)
   WHERE EXISTS (SELECT *
      FROM trans AS z
      WHERE  z.key = master.key)

-PatP

puzzzled · #3 (**permalink**) 12-01-04, 11:50

Thanks Pat, that does *exactly* what I need - especially as it's generic SQL, I need it to be portable.
Now to find out how well it performs compared with the procedural language approach!
- Steve (puzzzled)

Internet Services

Web Development

Digital Marketing

Consumer Tech