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:

**Table: sale**

id (int)
udid (int)
assid (int)

**Table: ud**

id (int)
assid (int)

`sale.assid` contains the correct value to update `ud.assid`.

What query will do this? I'm thinking of a `join` but I'm not sure if it's possible.

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

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:

[To see links please register here]

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

[PostgreSQL][1]:

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;


[1]:

[To see links please register here]

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.

I was thinking the SQL-Server one in the top post would work for Sybase since they are both T-SQL but unfortunately not.

For Sybase I found the update needs to be on the table itself not the alias:

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

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

The following statement with FROM keyword is used to update multiple rows with a join

UPDATE users
set users.DivisionId=divisions.DivisionId
from divisions join users on divisions.Name=users.Division

UPDATE tblAppraisalBasicData
SET tblAppraisalBasicData.ISCbo=1
FROM tblAppraisalBasicData SI INNER JOIN aaa_test RAN ON SI.EmpID = RAN.ID

And in MS ACCESS:

UPDATE ud
INNER JOIN sale ON ud.id = sale.udid
SET ud.assid = sale.assid;