Hi, I've got two tables. One contains a unique ID with two columns (each column contains a date). I need to update a second table, inputting the dates from the original two columns where the unique ID is the same in both tables.
DECLARE @Table1 TABLE
(
ID INT NOT NULL,
DT1 DATETIME NOT NULL,
DT2 DATETIME NOT NULL
);
INSERT INTO @Table1 (ID,DT1,DT2)
VALUES (1,'20150605','20150606'),
(6,'20150630','20150629'),
(8,'20150625','20150626'),
(9,'20150624','20150701');
DECLARE @Table2 TABLE
(
ID INT NOT NULL,
DT1 DATETIME NULL,
DT2 DATETIME NULL
);
INSERT INTO @Table2 (ID,DT1,DT2)
VALUES (1,NULL,NULL),
(2,'20150515','20150615'),
(6,NULL,NULL),
(8,NULL,NULL),
(9,NULL,NULL);
SELECT * FROM @Table1;
SELECT * FROM @Table2;
UPDATE T2
SET T2.DT1 = T1.DT1,
T2.DT2 = T1.DT2
FROM @Table2 AS T2
INNER
JOIN @Table1 AS T1
ON T1.ID = T2.ID
WHERE T2.DT1 IS NULL AND T2.DT2 IS NULL
SELECT * FROM @Table2;
I've included a check in the WHERE clause to only update if both DT cols are NULL, you can change this to your needs
Thanks for the quick reply, but those dates I used were only examples to show the information I'm needing. There are thousands of records that I need to do this for.
This approach should still work on larger datasets, unless we're talking a significant amount of rows, in which case you may wish to look into alternative methods.
Ok, I see your confusion. My code above is a small example of how to achieve what your requirements are in your OP. You would need to take what I've put and apply it to your real tables etc.