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 Thursday, April 4, 2013 11:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
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?
Post #1438933
Posted Thursday, April 4, 2013 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 12,927, Visits: 32,330
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1438952
Posted Thursday, April 4, 2013 12:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 1:21 PM
Points: 350, Visits: 158
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.
Post #1438954
Posted Thursday, April 4, 2013 12:13 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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
Post #1438961
Posted Thursday, April 4, 2013 6:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
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?
Post #1439061
Posted Thursday, April 4, 2013 6:49 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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
Post #1439066
Posted Thursday, April 4, 2013 7:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
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.
Post #1439068
Posted Thursday, April 4, 2013 7:42 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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
Post #1439072
Posted Thursday, April 4, 2013 8:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 5:29 PM
Points: 121, Visits: 300
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.
Post #1439074
Posted Thursday, April 4, 2013 8:31 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 19, 2014 3:52 PM
Points: 364, Visits: 385
Can you please reproduce the problem and post your table creation, data, and query scripts in this format?
Post #1439075
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse