How can I do an UPDATE statement with JOIN in SQL Server?

ID : 778

viewed : 116

Tags : sqlsql-servertsqlsql-server-2005sql-updatesql

Top 5 Answer for How can I do an UPDATE statement with JOIN in SQL Server?

vote vote

98

Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE):

ANSI/ISO:

update ud       set assid = (           select sale.assid            from sale            where sale.udid = ud.id      )  where exists (       select *        from sale        where sale.udid = ud.id  ); 

MySQL:

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

SQL Server:

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

PostgreSQL:

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

Note that the target table must not be repeated in the FROM clause for Postgres.

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 

SQLite:

update ud       set assid = (           select sale.assid            from sale            where sale.udid = ud.id      )  where RowID in (       select RowID        from ud        where sale.udid = ud.id  ); 
vote vote

80

This should work in SQL Server:

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

74

postgres

UPDATE table1 SET    COLUMN = value FROM   table2,        table3 WHERE  table1.column_id = table2.id        AND table1.column_id = table3.id        AND table1.COLUMN = value        AND table2.COLUMN = value        AND table3.COLUMN = value  
vote vote

64

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

52

PostgreSQL:

CREATE TABLE ud (id integer, assid integer); CREATE TABLE sales (id integer, udid integer, assid integer);  UPDATE ud SET assid = sales.assid FROM sales WHERE sales.id = ud.id; 

Top 3 video Explaining How can I do an UPDATE statement with JOIN in SQL Server?

Related QUESTION?