SQLTeam.com | Weblogs | Forums

Best way to join these two tables?


#1

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.

For example:

Table One:
ID DT1 DT2
1 06-05-15 06-06-15
6 06-30-15 06-29-15
8 06-25-15 06-26-15
9 06-24-15 06-01-15

Table 2:
ID DT1 DT2
1 NULL NULL
2 06-05-15 06-06-15
3 06-05-15 06-06-15
4 06-05-15 06-06-15
5 06-10-12 04-13-14
6 NULL NULL
7 06-05-15 06-06-15
8 NULL NULL
9 NULL NULL
10 06-05-15 06-06-15

I need to replace the NULL Values in Table 2 with the actual values from Table 1 where the unique ID are the same.


#2

Example below with some test data:

    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


#3

Dohsan,

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.


#4

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.


#5

I don't understand how it would work when you've put hard dates in the script.


#6

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.