Update with join

I am wondering how I can perform an update with a join, but only where there is a single match.

I have pasted some example code below, where I insert some data into 2 temporary tables.

The first has an ID
I want this ID to be put into the 2nd table based on conditions (in the join) that may or not join to a single record.

If this join results in joining in 2 records, I want this to not update, however using an update with a join seems to pick an ID at random.

The script pasted can be run (uses temporary tables) and shows what I mean. (I hope)

I used a column called OrderBy to see how random this is.

declare @tmp table ( tID uniqueidentifier, Field1 nvarchar(120), Field2 nvarchar(120), OrderBy int )

insert into @tmp values(newid(), '1' , '2', 2)
insert into @tmp values(newid(), '1' , '2', 1)
insert into @tmp values(newid(), '4' , '5', 1)
insert into @tmp values(newid(), '4' , '5', 2)
insert into @tmp values(newid(), '8' , '9', 1)

declare @tmp2 table ( tID uniqueidentifier, Field1 nvarchar(120), Field2 nvarchar(120) )

insert into @tmp2 values(null, '1' , '2')
insert into @tmp2 values(null, '4' , '5')
insert into @tmp2 values(null, '8' , '9')

select * from @tmp
select * from @tmp2

update @tmp2 set
	tID = T1.tID
from @tmp2 T2
inner join @tmp T1 on T1.Field1 = T2.Field1
and T1.Field2 = T2.Field2

select * from @tmp2

Open in new window

NarusegawaAsked:
Who is Participating?
 
knightEknightCommented:
You may want to do a select first to confirm it will update the rows (or at least the number of rows) you expect:

-- update @tmp2 set tID = T1.tID
select count(*)
from @tmp2 T2
join @tmp T1
  on T1.Field1 = T2.Field1
 and T1.Field2 = T2.Field2
join (select Field1, Field2 from @tmp group by Field1, Field2 having COUNT(*)=1) T3
  on T3.Field1 = T2.Field1
 and T3.Field2 = T2.Field2
0
 
knightEknightCommented:

update @tmp2 set tID = T1.tID
-- select *
from @tmp2 T2
join @tmp T1
  on T1.Field1 = T2.Field1
 and T1.Field2 = T2.Field2
join (select Field1, Field2 from @tmp group by Field1, Field2 having COUNT(*)=1) T3
  on T3.Field1 = T2.Field1
 and T3.Field2 = T2.Field2
0
 
NarusegawaAuthor Commented:
Looks okay, it works with the example I provided. I didn't think of doing a 2nd Join with a group by/count/having. Nice idea.

I'll just run this on my main test tables now and see if it updates the 2.5 million records as expected now.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
NarusegawaAuthor Commented:
Good point, thanks. I'll let you know how it goes :-)

I wasn't sure whether I was going to go down the route of ignore ones with multiple matches (as you've provided)

Or use only the top one from an OrderBy (i.e. the higest value in OrderBy). OrderBy in reality would've been a timestamp field. <-- But I think this way would require a cursor or something, not efficient.
0
 
SharathData EngineerCommented:
You can also try this.
declare @tmp table ( tID uniqueidentifier, Field1 nvarchar(120), Field2 nvarchar(120), OrderBy int )

insert into @tmp values(newid(), '1' , '2', 2)
insert into @tmp values(newid(), '1' , '2', 1)
insert into @tmp values(newid(), '4' , '5', 1)
insert into @tmp values(newid(), '4' , '5', 2)
insert into @tmp values(newid(), '8' , '9', 1)

declare @tmp2 table ( tID uniqueidentifier, Field1 nvarchar(120), Field2 nvarchar(120) )

insert into @tmp2 values(null, '1' , '2')
insert into @tmp2 values(null, '4' , '5')
insert into @tmp2 values(null, '8' , '9')

update t2 set t2.tID = t1.tID
 from @tmp2 t2 
 join (select Field1,Field2,tID,Count(*) over (partition by Field1,Field2) cnt 
        from @tmp) t1 
   on t1.Field1 = t2.Field1 and t1.Field2 = t2.Field2
where t1.cnt = 1

select * from @tmp2
/*
tID	Field1	Field2
NULL	1	2
NULL	4	5
A4899A56-54BE-4E3B-BC09-A320BBCEA251	8	9
*/

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update with join: my article to read:
http://www.experts-exchange.com/A_1517.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.