Sql Update Query

Question

I have a table T1 which contains three columns: Id, Name, Address

There is another table T2 which contains 2 columns Id, New_Address. Id column of T2 is same as of T1.

I need a query which will update Address column of T1 with New_Address of T2.

I can do it through a loop by checking ID and executing update statement. How can it has to be done with a query?

Solution 1

How about

UPDATE T1
SET Address = T2.New_Address
FROM T2
WHERE T1.id = T2.id

Solution 2

UPDATE T1
SET T1.Address = T2.New_Address
FROM T1
INNER JOIN T2 ON T2.ID = T1.ID

Solution 3

UPDATE T1
SET Address = (select New_Address from T2 where T1.ID=T2.ID );

Comments

[ypercubeᵀᴹ]

This - is equivalent to a LEFT JOIN and - will also update (setting them to NULL) all rows in Table1 that do not have a NewAdress in Table2.

[Martin Smith]

@ypercube - One possible advantage this query does have though is that it will raise an error if there is more than one matching new address rather than silently selecting an undeterministic one. It could be rewritten as SET Address = ISNULL((select New_Address from T2 where T1.ID=T2.ID ),Address); though MERGE gives this error checking built in too and would be preferable.