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
Share:
20,721
Jaan
Author by

Jaan

Updated on March 11, 2020

Comments

  • Jaan
    Jaan over 4 years

    I have one master table named table1 where i store or update mobileNo 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 unique mobileNo against table1. Now i want to update table2 against table1 if any data exists in table1.

    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.