SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


elegant strategies for complex update statements?


elegant strategies for complex update statements?

Author
Message
sqlguy-736318
sqlguy-736318
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 447
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?
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27875 Visits: 39919
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
SCH70
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 160
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
j.miner
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 358
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
sqlguy-736318
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 447
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
j.miner
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 358
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
sqlguy-736318
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 447
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
j.miner
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 358
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
sqlguy-736318
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 447
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
Mansfield
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 394
Can you please reproduce the problem and post your table creation, data, and query scripts in this format?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search