Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update with join

Posted on 2011-05-12
6
Medium Priority
?
282 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Narusegawa
6 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35747328

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
 

Author Comment

by:Narusegawa
ID: 35747408
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
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 35747430
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Narusegawa
ID: 35747485
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35748134
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35749191
update with join: my article to read:
http://www.experts-exchange.com/A_1517.html
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question