Tuesday, February 5, 2008

Join SQL Update

Folks,

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

As you can see in the example, this SQL re-queries calls twice - once for the update and once for the where.

Update join:
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

Much simpler, far more efficient, and once you get the hang of it - less prone to error when doing these sorts of updates.

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:

savvysteve said...

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.

Anonymous said...

Thank you for this post..

Anonymous said...

thank you for this post. works like a charm.

Anonymous said...

Thanks, this is a great example!

Anonymous said...

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!

Anonymous said...

Nice work, this is a really good bit of information thanks for that.

Matt Slay said...

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