SQL UPDATE with JOIN

Question

I need to update table in SQL Server 2005 with data from its 'parent' table, see below:

sale
---------
id (int)
udid (int)
assid (int)

ud
---------
id  (int)
assid  (int)

sale.assid contains the correct value and want to update ud.assid with its value. What query will do this? I'm thinking a join but I'm not sure if its possible.

Answer 1

This should work in MSSQL:

update ud 
set assid = sale.assid
from sale
where sale.udid = id

Answer 2

It very much depends on which database you're using. Here are the ways to do it in ANSI (aka should work on any database), MySQL, SQL Server, and Oracle. Be advised that the ANSI method will be much slower than the other two methods, but if you're not using MySQL, SQL Server, or Oracle, it's the only way to go.

ANSI:

update ud set assid = (select udid from sale where assid = ud.assid)

MySQL:

update ud u
inner join sale s on
    u.id = s.udid
set assid = s.assid

SQL Server:

update u
set assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

Oracle:

update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid

Answer 3

A standard SQL approach would be

UPDATE ud
SET assid = (SELECT assid FROM sale s WHERE ud.id=s.id)

On SQL Server you can use a join

UPDATE ud
SET assid = s.assid
FROM ud u
JOIN sale s ON u.id=s.id

Answer 4

Another example why SQL isn't really portable.

For MySQL it would be:

update ud, sale
set ud.assid = sale.assid
where sale.udid = ud.id;

For more info read multiple table update: http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]