UPDATE with MERGE


http://social.msdn.microsoft.com/Forums/en-US/ae2324e1-dc6a-4142-a446-956f27a408ed/update-one-table-with-the-max-value-of-another-table?forum=transactsql


CREATE TABLE [dbo].[General](
	[a_id] int NOT NULL,
	[a_date] datetime NOT NULL,
	[c_date] datetime NULL)


CREATE TABLE [dbo].[Specific](
	[a_id] int NOT NULL,
	[a_date] datetime NOT NULL,
	[b_id] int NOT NULL,
	[d_date] datetime NULL)

insert into General values (111,'Jan 1 2013', null)
insert into General values (112,'Jan 1 2013', 'Mar 10 2014')
insert into General values (113,'Jan 1 2013', null)
insert into General values (114,'Feb 1 2013', null)
insert into General values (115,'Feb 1 2013', 'Apr 1 2013')
insert into General values (116,'Feb 1 2013', 'Jan 1 1970')

insert into Specific values (111,'Jan 1 2013',1, 'Jan 1 2013')
insert into Specific values (111,'Jan 1 2013',2, 'Feb 1 2013')
insert into Specific values (111,'Jan 1 2013',3, 'Mar 1 2013')
insert into Specific values (112,'Jan 1 2013',1, 'Jan 1 2013')
insert into Specific values (112,'Jan 1 2013',2, 'Feb 1 2013')
insert into Specific values (113,'Jan 1 2013',1, 'Jan 1 1970')
insert into Specific values (114,'Feb 1 2013',1, null)
insert into Specific values (115,'Feb 1 2013',1, 'Jan 15 2013')
insert into Specific values (115,'Feb 1 2013',2, 'Feb 15 2013')
insert into Specific values (116,'Feb 1 2013',1, 'Jan 30 2013')
insert into Specific values (116,'Feb 1 2013',2, 'Jan 1 1970')


;with mycte as
(Select a_id,a_date,c_date from General WHERE c_date is null or c_date='1970-01-01')
Merge mycte as a
Using (Select a_id,a_date,Max(d_date) as d_date from specific 
WHERE d_date is not null or d_date<>'1970-01-01' Group by a_id,a_date) as b 
On a.a_id=b.a_id AND a.a_date=b.a_date 
WHen Matched Then
UPDATE
Set c_date = b.d_date;



select * from [General]


drop table [General],Specific


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s