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?

score 19 · Accepted Answer · answered Feb 23 '13 at 11:33

19

How about

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

answered Feb 23 '13 at 11:33

marc_s

score 10 · Answer 2 · edited Feb 23 '13 at 11:42

10

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

edited Feb 23 '13 at 11:42

Joachim Isaksson

answered Feb 23 '13 at 11:34

Darren

score 4 · Answer 3 · edited Feb 23 '13 at 11:38

4

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

edited Feb 23 '13 at 11:38

Mahmoud Gamal

answered Feb 23 '13 at 11:37

Piyush

2

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. – ypercubeᵀᴹ Feb 23 '13 at 11:55
1

@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. – Martin Smith Feb 23 '13 at 12:12

3 Answers3