Don't know about you but I've wondered on occasion whether or not it's possible to do a join update in Oracle and SQL Server as I've seen done in mySQL. For those who've never seen an update join, here's one to illustrate:
Old school:
update invc_itemAs you can see in the example, this SQL re-queries calls twice - once for the update and once for the where.
set feed_id =
(select max(feed_id)
from calls
where invc_item_id = invc_item.invc_item_id)
where feed_id = 0
and exists
(select *
from calls
where invc_item_id = invc_item.invc_item_id
and feed_id > 0)
Update join:
update iMuch simpler, far more efficient, and once you get the hang of it - less prone to error when doing these sorts of updates.
set i.feed_id = c.feed_id
from invc_item i, calls c
where i.feed_id = 0
and c.invc_item_id = i.invc_item_id
and c.feed_id > 0
I'm not sure whether or not you have to use the table aliases as I did. Haven't tested without. This is how it was illustrated in the documentation I found.
Now for the bad news... SQL Server (the toy wannabe database) supports it, Oracle does not.
Thanks for the help Scott...
7 comments:
I just did something very similar to the second example and it worked like a champ!!! This was a great help.
"update i
set i.feed_id = c.feed_id
from invc_item i, calls c
where i.feed_id = 0
and c.invc_item_id = i.invc_item_id
and c.feed_id > 0"
Note that the "i" is your tablename. The trick here is NOT to use a table alias for the table you are updating. Then use the full table name to alias your columns on the where clause. Once I got that figured out my query ran... and SCORE!!! Saved me a ton of time.
Good trick, another one for my sleeve.
Thank you for this post..
thank you for this post. works like a charm.
Thanks, this is a great example!
Great example, could this work with conditions placed oin a thrid tables as well?
I want to update a value in colmun on table 1 with a value from table 2 providing following conditions are met:
Where Table1.column1=Table2.column1 and Table2.column2=Table3.column2
Having trouble writng the sql statement to support this update.
Thanks in advance!
Nice work, this is a really good bit of information thanks for that.
You can move the Join portion outside of the Where clause area and it makes it a little clearer that you are actually doing a Join operation, and it makes the Where clause a little clearer as well:
Update i
Set i.feed_id = c.feed_id
From invc_item i Join calls c on c.invc_item_id = i.invc_item_id
Where i.feed_id = 0 and c.feed_id
Post a Comment