Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

elegant strategies for complex update statements? Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 1:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 7,176, Visits: 13,623
sqlguy-736318 (4/4/2013)
What I'm suggesting is that table #a would have 2 rows with the same company. For example:

'Microsoft', 'Seattle'
'Microsoft', 'Portland'

In this scenario, row #1 would get updated but row #2 would not.


That's incorrect, you must be observing something different. Using sample script from above, try this:

create table #a (DomainName varchar(25), OfficeName varchar(25));

create table #b (DomainName varchar(25), OfficeName varchar(25));

insert into #a
values
('microsoft', 'seattle'),
('microsoft', 'Reading'),
('dell', 'huston');

insert into #b
values
('microsoft', 'seattle, wa'),
('dell', 'huston, tx');

select * from #a;

update a
set OfficeName = b.OfficeName
from #a a
inner join #b b on b.DomainName = a.DomainName;

select * from #a;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1439121
Posted Friday, April 5, 2013 1:49 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:18 AM
Points: 80, Visits: 348


http://craftydba.com/?attachment_id=5149


Hi SQL Guy,

Unless you try the sample code that I gave you, we really do not know what you are trying to do.

I updated table A to have two records. Table B has one matching record.

After the update, both records in table are updated.

Works like I think it would!

Cheers[url=http://craftydba.com/?attachment_id=5149][/url]


John Miner
Crafty DBA
www.craftydba.com
Post #1439455
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse