elegant strategies for complex update statements?

Question

Post reply

elegant strategies for complex update statements?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
April 4, 2013 at 11:27 am

#273140

Hello - What are some elegant strategies for implementing update statements? For example, updating a column value for every row in a table based on a specific subquery?
I was thinking about using a table variable with a cursor but I'm guessing a SQL guru could recommend a better, more elegant, more integrated approach?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

sql has the ability to update a table based on another table, you just need to get used to the syntax.

the advantage is it would be set based,and allow you to avoid a cursor or loop.

If you can post some details, we could help

UPDATE myTarget

here's a mockup example:

UPDATE myTarget

SET myTarget.ColumnName = OtherTable.OtherColumnName

FROM myRealTable myTarget --an alias on the real table to update

LEFT OUTER JOIN OtherTable

ON myTarget.PK = OtherTable.PK

WHERE myTarget.StatusColumn IS NULL

AND OtherTable.ProcessedDate IS NOT NULL

SET myTarget

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SCH70 Right there with Babe Points: 758 More actions · Answer 2

There are options like using joins or correlated sub-queries, but without having a better idea of what you're trying to accomplish, its really hard to come up with concrete suggestions.

j.miner SSCrazy Points: 2264 More actions · Answer 3

It all depends on the situation.

Stay away from Cursors if the data set is large since they are inherently slow.

Sometimes, Extract, Translate, and Load (ETL) programmers will create a patch (update) table with the key value from table and any fields/values they want to update.

Then, you can run a single T-SQL statement to perform the update.

This pushes the update task away from the ETL server and onto the database server.

However, you have to be mindful of log file growth when doing large updates.

Doing a backup or snapshot before and after the change, changing the recovery model to simple and back to full, and performing the updates in small batches can reduce log file growth.

In summary, it all depends ...

Good testing of your solution is key to success!

John Miner
Crafty DBA
www.craftydba.com

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 4

I've created the following update query which joins to another table:

-- update new_ae.new_aeoffice with proper office name

update new_ae

set new_aeoffice = ado.OfficeName

from new_ae

join #AE_Domain_Office ado on new_ae.New_DomainName = ado.DomainName

The issue I'm having is that #AE_Domain_Office has 2 rows with a matching domain name so this update statement is only updating the first row with the expected office name. How can I adjust this query so additional matching rows in #AE_Domain will get updated?

j.miner SSCrazy Points: 2264 More actions · Answer 5

Hi SQL Guy,

Please post a sample structure for the two tables with some sample data.

This will help us determine what is wrong with the TSQL.

Thanks

John Miner
Crafty DBA
www.craftydba.com

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 6

I think for the purpose of this example it should be sufficient to assume that each table has 2 columns - DomainName and OfficeName, with the column names between the tables spelled slightly differently.

j.miner SSCrazy Points: 2264 More actions · Answer 7

Again, real data would help me help you!

Here is a shot in the dark, table #a has a one-to-one relationship with table #b. Update work fine!

create table #a

(

DomainName1 varchar(25),

OfficeName1 varchar(25)

);

create table #b

(

DomainName2 varchar(25),

OfficeName2 varchar(25)

);

insert into #a values

('microsoft', 'seattle'),

('dell', 'huston');

insert into #b values

('microsoft', 'seattle, wa'),

('dell', 'huston, tx');

select * from #a;

select * from #b;

update #a

set OfficeName1 = OfficeName2

from

#a inner join #b on #a.DomainName1 = #b.DomainName2;

select * from #a;

John Miner
Crafty DBA
www.craftydba.com

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 8

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.

Mansfield SSCrazy Points: 2366 More actions · Answer 9

Can you please reproduce the problem and post your table creation, data, and query scripts in this format[/url]?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

j.miner SSCrazy Points: 2264 More actions · Answer 11

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

John Miner
Crafty DBA
www.craftydba.com