How can I do an UPDATE statement with JOIN in SQL Server?
I need to update this table in SQL Server with data from its 'parent' table, see below:
id (int) udid (int) assid (int)
id (int) assid (int)
sale.assid contains the correct value to update
What query will do this? I'm thinking of a
join but I'm not sure if it's possible.
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
update ud set assid = ( select sale.assid from sale where sale.udid = ud.id ) where exists ( select * from sale where sale.udid = ud.id );
update ud u inner join sale s on u.id = s.udid set u.assid = s.assid
update u set u.assid = s.assid from ud u inner join sale s on u.id = s.udid
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.
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
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 );
Read more... Read less...
This should work in SQL Server:
update ud set assid = sale.assid from sale where sale.udid = id
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
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
Simplified update query using JOIN-ing multiple tables.
UPDATE first_table ft JOIN second_table st ON st.some_id = ft.some_id JOIN third_table tt ON tt.some_id = st.some_id ..... SET ft.some_column = some_value WHERE ft.some_column = 123456 AND st.some_column = 123456
Note - first_table, second_table, third_table and some_column like 123456 are demo table names, column names and ids. Replace them with the valid names.