multiple update statements to update the same table

Question

Post reply

multiple update statements to update the same table

sqlfriend

SSC Guru

Points: 52794
More actions
February 9, 2012 at 5:21 pm

#264359

I have a maintenance sql script, that has basically 6 update statements.
All the 6 statements are update the same table.
The table has 1660383 rows.
My script is written the order like this:
First update statement
Second update statement
Third update statement......
My question is if I copy all the 5 statements together into one query window, and hit Excute,
will that execute one by one, could that possibley happen that the first one has not finished the second start to update, any lock issues?
how to avoid problem like this, should I add something like GO between each update statement.
or shall I add a WAITFOR DELAY '0:10'; in between each update statements?
Or it should not be a problem because they will execute sequentially?
Thanks

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Do you need to have them as 6 separate updates?

Have you considered updating this process to use only one update statement?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 2

sqlfriends (2/9/2012)
My question is if I copy all the 5 statements together into one query window, and hit Excute,
will that execute one by one, could that possibley happen that the first one has not finished the second start to update, any lock issues?
Or it should not be a problem because they will execute sequentially?

The statements will execute sequentially. Each one will wait until the one before it has run. Just like programming in most languages, each statement must complete before the next one can start. There should be no issues running all statments together.

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

Thank you, I tried it , it works fine although the table is big.

For the other post who asks me if I can combine them together, my answer is no, because they have different joins with tables, and different business rule in it.

Thanks

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 4

I think his mindset is just fine, Joe. It is frequently much faster to employ such "Divide'n'Conquer" methods rather than trying to do everything in a single query. After all, it's not like we have to tolerate a "rewind" for a mag tape anymore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

I must agree with Joe this time. I understand his example & serious consequences if OP’s case follows the same pattern.

If the initial price of a book is $24 (<25), it will be updated to $26.40. In second update it qualifies for price update again (>=25) and second updated value would be $25.08. This is not the correct result per requirements.

IIRC there was a BUG logged for SQL Server (some version) for it (multiple updates) which got corrected later on.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Multiple updates that have different joins, different conditions must be combined into a single update?

So

Update TableA

SET <whatever>

FROM TableA inner join TableB on <someCondition>

Inner Join TableC On <someOtherCondition>

Where TableB.SomeColumn = SomeValue and TableA.AnotherColumn = SomeOtherValue AND TableC.YetAnotherColumn = AThirdCondition

and

Update TableA

SET <whatever>

FROM TableA inner join TableD on <someCondition>

Inner Join TableE On <someOtherCondition>

Where TableD.SomeColumn = SomeValue and TableD.AnotherColumn = SomeOtherValue AND TableE.YetAnotherColumn = AThirdCondition

Should actually be a single update and there will be serious consequences is they're not?

Joe's example is academic in its simplicity and can trivially be made into one update. That doesn't mean that all sequences of updates are simple and can trivially be combined

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

This is what I said...

I must agree with Joe this time. I understand his example & serious consequences if OP’s case follows the same pattern.

I know OP has already declared that different updates have different predicates. But there is no harm in cross-checking them for loopholes if any. Who knows he could really merge one or two?

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

CELKO (2/12/2012)
Dev (2/12/2012)
This is what I said...
I must agree with Joe this time. I understand his example & serious consequences if OP’s case follows the same pattern.
I know OP has already declared that different updates have different predicates. But there is no harm in cross-checking them for loopholes if any. Who knows he could really merge one or two?
How about a MERGE statement?
Five or six UPDATEs will lock the base table for a loooong time, so I have to disagree with Jeff on this point.

I don't think I can use merge or combine the update statements.

Actually when I run the query, it only takes less than 1 minute.

I attached query in the attachment

Paul White SSC Guru Points: 150468 More actions · Answer 9

The updates probably could be compacted quite a bit, but I don't have time right now for a complete rewrite. One thing I would recommend is that you dump the COALESCE (with magic -20 values) for something more elegant that usually performs a bit better too:

UPDATE o

SET

o.elAttendanceAreaID = n.elAttendanceAreaID,

o.msAttendanceAreaID = n.msAttendanceAreaID,

o.hsAttendanceAreaID = n.hsAttendanceAreaID,

o.ChangeDt = SYSDATETIME(),

o.ChangedBy = SYSTEM_USER

FROM dbo.gisGeographicLookup AS o

JOIN dbo.gisGeographicLookup_sq21 AS n ON

o.gisGeographicLookupID = n.gisGeographicLookupID

AND o.SchoolYear=n.schoolyear

WHERE

o.schoolyear = 2012

-- Use this instead of the COALESCE-with-magic-values

-- See http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

AND NOT EXISTS

(

SELECT o.elAttendanceAreaID, o.msAttendanceAreaID, o.hsAttendanceAreaID

INTERSECT

SELECT n.elAttendanceAreaID, n.msAttendanceAreaID, n.hsAttendanceAreaID

);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

sqlfriend SSC Guru Points: 52794 More actions · Answer 10

Thanks, in the query, you use:

SELECT .elAttendanceAreaID, o.msAttendanceAreaID, o.hsAttendanceAreaID

INTERSECT

SELECT .elAttendanceAreaID, n.msAttendanceAreaID, n.hsAttendanceAreaID

Does the first .elAttendanceAreaID, you dropped O, and second one you missed n.

Just want to make sure it is not something new I don't know.

I think the query is more elegant. For performance it seems use a little less memory. the Exeuction time is almost same.

Thank you

Paul White SSC Guru Points: 150468 More actions · Answer 11

sqlfriends (2/15/2012)
Does the first .elAttendanceAreaID, you dropped O, and second one you missed n.

Ha! Weird typo - not sure how that happened, but I have fixed the post now.

I think the query is more elegant. For performance it seems use a little less memory. the Exeuction time is almost same.

It's certainly is more elegant - and you don't have to worry about choosing a magic value (like -20) that doesn't appear in the data (and never will).

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

multiple update statements to update the same table

Cookies on SQLServerCentral