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...