Geert Verhoeven
Hi,
Here is the code of the setup and an example of the update statement:
/* SETUP
CREATE TABLE TABLE1
(
COL_A VARCHAR(50),
COL_B VARCHAR(50),
COL_C INT
)
INSERT INTO TABLE1 VALUES (null, null, 1)
INSERT INTO TABLE1 VALUES (null, null, 2)
INSERT INTO TABLE1 VALUES ('Red', 'Green', 3)
CREATE TABLE TABLE2
(
COL_A VARCHAR(50),
COL_B VARCHAR(50),
COL_C INT,
COL_D VARCHAR(50)
)
INSERT INTO TABLE2 VALUES ('Blue', 'Pink', 2, 'Mars')
INSERT INTO TABLE2 VALUES ('Blue', 'Pink', 2, 'Jupiter')
INSERT INTO TABLE2 VALUES ('Blue', 'Pink', 2, 'Uranus')
INSERT INTO TABLE2 VALUES ('Orange', 'Brown', 1, 'Saturn')
INSERT INTO TABLE2 VALUES ('Orange', 'Brown', 1, 'Earth')
INSERT INTO TABLE2 VALUES ('Red', 'Green', 3, 'Neptune')
*/
UPDATE
TABLE1
SET TABLE1.COL_A = FILTEREDTABLE2.COL_A,
TABLE1.COL_B = FILTEREDTABLE2.COL_B
FROM TABLE1
INNER JOIN (SELECT DISTINCT COL_A, COL_B, COL_C FROM TABLE2) FILTEREDTABLE2
ON TABLE1.COL_C = FILTEREDTABLE2.COL_C
WHERE TABLE1.COL_A IS NULL
If you run this, you will see that 2 records have been updated. A small question, what if only COL_B has nulls It is possible to set this too like this:
UPDATE
TABLE1
SET TABLE1.COL_A = ISNULL(TABLE1.COL_A, TABLE2.COL_A),
TABLE1.COL_B = ISNULL(TABLE1.COL_B, TABLE2.COL_B)
FROM TABLE1
INNER JOIN (SELECT DISTINCT COL_A, COL_B, COL_C FROM TABLE2) TABLE2
ON TABLE1.COL_C = TABLE2.COL_C
WHERE TABLE1.COL_A IS NULL OR TABLE1.COL_B IS NULL
With the above statement, COL_A and COL_B are only updated if they are null.
Greetz,
Geert
Geert Verhoeven
Consultant @ Ausy Belgium
My Personal Blog