Merge 2 Tables with Row_Number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Merge 2 Tables with Row_Number

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-01-05 : 16:38:01

I am trying to Merge 2 tables (imported from another server) using ROW_NUMBER because I only want the most current Update. Using Left Join because there may not be a match in Right table. I know what the error means but have not been able to figure out how to fix it.

I get the error in the 'SET' & 'INSERT' commands.

The error I get is:


Msg 4104, Level 16, State 1, Line xx
The multi-part identifier "E2.HomeEmail" could not be bound.


MERGE EMPLOYEE AS EM
	USING
		(Select T.EID, T.Col2, T.COL3, T.DateChanged
, E2.HomeEmail
		from
			(Select EID, Col2, Col3, DateChanged
, 
				ROW_NUMBER() OVER(PARTITION BY EID ORDER BY DateChanged
 DESC) AS RowNum
			from TEMPTABLE) AS T
			left join EMPLMAIL AS E2 
			on T.EID = E2.EID
			where DateChanged
 >= dateadd(day,datediff(day,0,getdate())-1,0) and RowNum = 1) AS i
			
	ON (EM.EID = i.EID)
	WHEN MATCHED 
	   THEN UPDATE SET
		EM.Col1	= i.Col1,
		EM.Col2 = i.Col2,
		EM.HomeEmail = (CASE when E2.HomeEmail is NULL then '' else E2.HomeEmail END),
		EM.DateChanged
 = i.DateChanged
	WHEN NOT MATCHED
	THEN
		INSERT (EID, Col1, Col2, HomeEmail, DateChanged
)
		VALUES (i.EID, i.Col1, i.Col2,
			(CASE when E2.HomeEmail is NULL then '' else E2.HomeEmail END),
			i.DateChanged
)				

	;

TEMPTABLE Table - Imported
EID int
Col1 char(2)
Col2 char(2)
DateChanged datetime

DATA:
1234 'AA' 'ZZ' 2011-01-05 10:48:10.000
1234 'AA' 'ZZ' 2011-01-05 12:48:10.613
3333 'NN' 'XX' 2011-01-05 12:48:10.613
5555 'QQ' 'BB' 2011-01-05 12:48:10.613
7878 'YY' 'YY' 2011-01-05 12:48:10.613

EMPLMAIL Table - Imported
EID int
HomeEmail char(50)

DATA:
1234 'Mine@email'
3333 NULL
7878 'Your@email'

RESULTS:
EMPLOYEE Table
EID int
Col1 char(2)
Col2 char(2)
HomeEmail char(50)
DateChanged datetime

DATA:
1234 'AA' 'ZZ' 'Mine@email' 2011-01-05 12:48:10.613
3333 'NN' 'XX' [BLANKS] 2011-01-05 12:48:10.613
5555 'QQ' 'BB' [BLANKS] 2011-01-05 12:48:10.613
7878 'YY' 'YY' 'Your@email' 2011-01-05 12:48:10.613

Thanks.

Forgot to mention that when I pull the 'Select' statements out of the MERGE it returns the desired results in Microsoft SQL Server Management Studio.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-05 : 17:17:33

The SOURCE CTE is referenced as i. E2 is not visible to the UPDATE SET Target = Source statement below. Change E2. to i. Also, you can is ISNULL() instead of the case when else end




MERGE EMPLOYEE AS EM
	USING
		(Select   T.EID
				, T.Col2 
				, T.COL3
				, T.DateChanged
				, E2.HomeEmail
		  from
				(Select EID, Col2, Col3, DateChanged
,					ROW_NUMBER() OVER(PARTITION BY EID ORDER BY DateChanged
				 DESC) AS RowNum
				 from TEMPTABLE) AS T
			left join EMPLMAIL AS E2 
				on T.EID = E2.EID
		 where DateChanged
		>= dateadd(day,datediff(day,0,getdate())-1,0) and RowNum = 1) 
				AS i ---this is your SOURCE table for the UPDATE
			
	ON (EM.EID = i.EID)
	WHEN MATCHED 
	   THEN UPDATE SET
		EM.Col1	= i.Col1,
		EM.Col2 = i.Col2,
		EM.HomeEmail =ISNULL(i.HomeEmail,''),
		EM.DateChanged = i.DateChanged
	WHEN NOT MATCHED
	THEN
		INSERT (EID, Col1, Col2, HomeEmail, DateChanged)
		VALUES (i.EID, i.Col1, i.Col2,ISNULL(i.HomeEmail,''),i.DateChanged)

Poor planning on your part does not constitute an emergency on my part.

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-01-06 : 10:18:50

Thank You very much. And thanks for the ISNULL option, I am always for less coding.

I should have seen this but being a newbie to SQL Server 2008 & no formal training in it has its challenges. That is why I am glad I found this Forum. I've learned so much from everyone.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources