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

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 uinner join sale s on    u.id = s.udidset u.assid = s.assid

SQL Server:

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

PostgreSQL:

update ud  set assid = s.assidfrom 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) upset 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 );

This should work in SQL Server:

update ud set assid = sale.assidfrom salewhere sale.udid = id

postgres

UPDATE table1SET    COLUMN = valueFROM   table2,       table3WHERE  table1.column_id = table2.id       AND table1.column_id = table3.id       AND table1.COLUMN = value       AND table2.COLUMN = value       AND table3.COLUMN = value