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 «««1234»»

UPDATE using a self-join Expand / Collapse
Author
Message
Posted Tuesday, January 18, 2011 7:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 15, 2015 6:10 AM
Points: 13,872, Visits: 9,606
tfifield (1/14/2011)
Damn! I thought I'd never say this but score 1 for Joe.

Actually the FROM syntax is very useful as a previous poster mentioned in that the UPDATE can be commented out and replaced with a SELECT to see the count of rows. I can quickly eyeball what's about to be updated. Afte that I usually put a GROUP BY HAVING COUNT(*) > 1 in the select just to make sure there isn't something that I don't know about in the data. Not a perfect solution since it could pop up periodically.
Todd Fifield


You can still get more than an idea of how the update will end up looking.

begin transaction;

merge ...
output inserted.*;

commit;

Don't run the commit till you're happy with the data in the output grid.

If you don't want to hold a transaction open, use rollback on the test run, then commit when you're sure it's giving you what you want.

The Output clause has been possible since SQL 2005, and is better than eyeballing a Select version of an Update syntax, since you don't have to change any of the command in order to run it.

Edit: For clarity.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1049316
Posted Saturday, January 29, 2011 1:42 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, February 24, 2015 3:25 PM
Points: 959, Visits: 2,890
Gus,
You busted my chops on this one. I currently only have 1 client on 2008 so I don't actually user the MERGE statement much.

The idea of dumping the output into a table variable that won't be affected by a ROLLBACK is very good. I'm going to use it next time.
Todd Fifield
Post #1055822
Posted Monday, January 31, 2011 4:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:54 AM
Points: 359, Visits: 244
I'm with Jeff. The update gives me exactly what I would expect.

Post #1056070
Posted Tuesday, February 1, 2011 4:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 6,149, Visits: 7,051
CELKO (1/31/2011)
Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target.


I haven't moved up to SQL 2k8 yet, so haven't had a chance to use MERGE. But that's good information to know that I didn't know before. Thanks for mentioning it.

CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.


Brandie Tarvin, MCITP Database Administrator

LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1056731
Posted Tuesday, February 1, 2011 7:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,012, Visits: 7,886
Brandie Tarvin (2/1/2011)
CELKO (1/31/2011)
Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target.


I haven't moved up to SQL 2k8 yet, so haven't had a chance to use MERGE. But that's good information to know that I didn't know before. Thanks for mentioning it.

CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.


Would FoxPro count?


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #1056819
Posted Tuesday, February 1, 2011 7:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 20,626, Visits: 9,649
Stefan Krzywicki (2/1/2011)
Brandie Tarvin (2/1/2011)
CELKO (1/31/2011)
Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target.


I haven't moved up to SQL 2k8 yet, so haven't had a chance to use MERGE. But that's good information to know that I didn't know before. Thanks for mentioning it.

CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.


Would FoxPro count?


Would filemaker count?
Post #1056820
Posted Tuesday, February 1, 2011 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 15, 2015 6:10 AM
Points: 13,872, Visits: 9,606
Stefan Krzywicki (2/1/2011)
Brandie Tarvin (2/1/2011)
CELKO (1/31/2011)
Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target.


I haven't moved up to SQL 2k8 yet, so haven't had a chance to use MERGE. But that's good information to know that I didn't know before. Thanks for mentioning it.

CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.


Would FoxPro count?


Only if you also count CSV files as "databases".


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1056823
Posted Tuesday, February 1, 2011 7:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,012, Visits: 7,886
GSquared (2/1/2011)
Stefan Krzywicki (2/1/2011)
Brandie Tarvin (2/1/2011)
CELKO (1/31/2011)
Actually, INSERT and UPDATE are single table operations, while MERGE requires a source and target.


I haven't moved up to SQL 2k8 yet, so haven't had a chance to use MERGE. But that's good information to know that I didn't know before. Thanks for mentioning it.

CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.


Would FoxPro count?


Only if you also count CSV files as "databases".


I've never used FoxPro, but I know people who swear by it.


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #1056829
Posted Tuesday, February 1, 2011 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 6,149, Visits: 7,051
GSquared (2/1/2011)
Stefan Krzywicki (2/1/2011)
Brandie Tarvin (2/1/2011)
CELKO (1/31/2011)

Successful projects start on lesser SQLs (even ACCESS ) then have to move to DB2, Oracle, Teradata or whatever.


Including Access? What other lesser SQLs are there? I'm assuming you mean non-robust solutions, so I'm truly curious as Access (and maybe Lotus) is the only one I know for sure.


Would FoxPro count?


Only if you also count CSV files as "databases".


Don't get me started. I know Business Users who swear Excel is a database. <*headdesk*>


Brandie Tarvin, MCITP Database Administrator

LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1056849
Posted Tuesday, February 1, 2011 1:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 12, 2015 1:08 PM
Points: 3,688, Visits: 72,447
Brandie Tarvin (2/1/2011)

Don't get me started. I know Business Users who swear Excel is a database. <*headdesk*>


It isn't? :P
I know I've seen people make the case that Vlookup is superior to JOIN because you do the join and get data back at the same time




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1057038
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse