Using an inner join with subqueries in an update syntax

Question

I am trying to use a inner join with an update statement with a subquery ... can you help me out with the sytax please --- and also how do you use the AS clause for alias in sql server??? the following is what i am trying to do :

Update Table1 
inner join table2
set table1.value1 = (select table2.value1 where table1.value 1 ....)

any idea??

Answer 1

If you need to use a subquery to perform the UPDATE you can do it this way:

UPDATE t1
SET t1.value = t2.value
FROM Table1 t1
JOIN
(
    SELECT id, value
    FROM table2
) t2
ON t1.id = t2.id