Unanswered: selective update master table from another table
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:
Transaction has something like:
(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!
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?
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)