Update multiple column from another table using sql server 2005
20,721
Perhaps NULL values from TABLE2 can be avoided to UPDATE NOT NULL values in TABLE1 using following UPDATE statement.
Only ISNULL function is added to previous post
update table1
set
date1 = ISNULL(t2.date1, date1),
date2 = ISNULL(t2.date2, date2)
from table2 t2
where table1.mobileno = t2.mobileno
Author by
Jaan
Updated on March 11, 2020Comments
-
Jaan over 4 years
I have one master table named
table1
where i store or updatemobileNo
data daily for a month.;WITH table1 AS (SELECT * FROM (VALUES (9999999999, '01/10/2013', NULL, NULL, NULL, NULL), (9999999999, NULL, '02/10/2013', NULL, NULL, NULL), (9999999999, NULL, NULL, '03/10/2013', NULL, NULL), (9999999999, NULL, NULL, NULL, '04/10/2013', NULL), (9999999999, NULL, NULL, NULL, NULL, '30/10/2013'), (9999999999, NULL, NULL, NULL, NULL, NULL), (8888888888, '01/10/2013', NULL, NULL, NULL, NULL), (8888888888, NULL, '02/10/2013', NULL, NULL, NULL), (8888888888, NULL, NULL, '03/10/2013', NULL, NULL), (8888888888, NULL, NULL, NULL, '04/10/2013', NULL), (8888888888, NULL, NULL, NULL, NULL, '30/10/2013')) as t(mobileno,date1,date2,date3,date4,date30))
And i have another table named
table2
where i keep uniquemobileNo
against table1. Now i want to updatetable2
againsttable1
if any data exists intable1
.mobileno date1 date2 date3 date4 date30 --------------- ---------- ---------- ---------- ---------- ---------- 8888888888 01/10/2013 02/10/2013 03/10/2013 04/10/2013 30/10/2013 9999999999 01/10/2013 02/10/2013 03/10/2013 04/10/2013 30/10/2013
However i tried the query like this
UPDATE table1 set table1.date1 = (SELECT date1 from table2 where table2.mobileno = table1.mobileno) Where table2.mobileno = table1.mobileno
How do i update in a single query without repeating to update the 30 nos. of date columns, please help me. Thanks in advance.