Systems Engineering and RDBMS

FROM clause in the UPDATE statement

Posted by decipherinfosys on June 12, 2009

Yesterday, a friend asked why this statement was failing in Oracle:

UPDATE item_master
SET mf_item_number = gm.SKU
FROM
item_master im
JOIN group_master gm ON im.sku=gm.sku
JOIN Manufacturer_Master mm ON gm.ManufacturerID=mm.ManufacturerID
WHERE im.mf_item_number like 'STA%' AND gm.manufacturerID=34

Now, there are some other issues as well in this SQL (keeping the table relationships in mind) but the main thing to point out is that in ANSI standards, FROM is not allowed in the UPDATE statement.  That is an extension in T-SQL that is available to the users of SQL Server.  Take a look at our post which shows how to update a table with data from another table and it covers the big three RDBMS (Oracle, SQL Server and DB2 LUW):

https://decipherinfosys.wordpress.com/2007/01/31/update-data-in-one-table-with-data-from-another-table/

Also, this query can be re-written to remove the FROM clause and use an EXISTS condition – read up here on some general guidelines for making use of the EXISTS condition – it is not a panacea for performance issues so understand when it makes sense to use it.

References:

  • BOL entry on the Update statement – here.
  • Post by Microsoft MVP Hugo Kornelis – here.

One Response to “FROM clause in the UPDATE statement”

  1. civilclub said

    VERY Good.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: