SQL - Updating Multiple Tables

[BobT]

I have the following section of code:

begin transaction
UPDATE CPB_BATCHPARCEL
SET CPB_BATCHPARCEL.PARCEL_USER_ID = 'DTM',
CPB_BATCHPARCEL.NODE_ID = 26
FROM CPB_BATCHPARCEL INNERJOIN CPB_DOCUMENT
ON CPB_BATCHPARCEL.PARCEL_ID = CPB_DOCUMENT.PARCEL_ID
WHERE CPB_BATCHPARCEL.NODE_ID = 25 AND CPB_DOCUMENT.DOC_TYPE LIKE 'MH%'
if @@error <> 0 goto rbck
UPDATE CPB_DOCUMENT
SET CPB_DOCUMENT.[USER_ID] = 'DTM',
CPB_DOCUMENT.NODE_ID = 26
FROM CPB_DOCUMENT INNERJOIN CPB_BATCHPARCEL
ON CPB_BATCHPARCEL.PARCEL_ID = CPB_DOCUMENT.PARCEL_ID
WHERE CPB_BATCHPARCEL.NODE_ID = 26
if @@error <> 0 goto rbck
commit transaction

rbck:
rollback transaction

If the first part of the transaction is true and update occurs, the second
part of the transaction will need to update other fields. My understanding
is that what is fetched for the first transaction will not be related to the
second transaction. Should I be using a cursor here which I understand is
much slower?

Thanks,
--
BT

[ML]

I see no reason why a cursor should be used.

Have you tested the query? Does it yield the expected/desired results?

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

[sloan]

You'll also need a "Goto Finish" line...after the commit trans.

begin transaction

UPDATE CPB_BATCHPARCEL
SET CPB_BATCHPARCEL.PARCEL_USER_ID = 'DTM',
CPB_BATCHPARCEL.NODE_ID = 26
FROM CPB_BATCHPARCEL INNERJOIN CPB_DOCUMENT
ON CPB_BATCHPARCEL.PARCEL_ID = CPB_DOCUMENT.PARCEL_ID
WHERE CPB_BATCHPARCEL.NODE_ID = 25 AND CPB_DOCUMENT.DOC_TYPE LIKE 'MH%'

if @@error <> 0 goto ROLLBACKLINE:

UPDATE CPB_DOCUMENT
SET CPB_DOCUMENT.[USER_ID] = 'DTM',
CPB_DOCUMENT.NODE_ID = 26
FROM CPB_DOCUMENT INNERJOIN CPB_BATCHPARCEL
ON CPB_BATCHPARCEL.PARCEL_ID = CPB_DOCUMENT.PARCEL_ID
WHERE CPB_BATCHPARCEL.NODE_ID = 26

if @@error <> 0 goto ROLLBACKLINE

commit transaction
GOTO FINISHLINE:

ROLLBACKLINE:
rollback transaction

FINISHLINE:

print 'Im done'

Since you're updating 2 different tables, you probably won't have an issue.
But uber-stress, lots of activity, it just sometimes depends.

I'm not sure what you mean by "the first query is true". even if no rows
meet the criteria, it'll run sucessfully.

like this

Update Orders Set OrderDate = GETDATE() where OrderID = -88888888

This will run correctly, but just won't update anything if nothing meets the
criteria.

You could check the @@ROWCOUNT after the first query..maybe that's what you
mean?....

"BobT" <Bo...@discussions.microsoft.com> wrote in message
news:0D8438EE-0173-4F5F...@microsoft.com...

[BobT]

Sloan, ML,

Thanks for the details. The code ran fine. I was having a syntax error
with the label: reference in Toad....worked fine in WinSQL though with no
problems...

--
BT

[BobT]

One final question regarding multiple updates on tables in transaction.

If I have first statement in transaction as:
Update test Set field1 = 5

If @@error <> 0 goto Rollback

Update test set field2 = '1st' where field1 =5

If @@error <> 0 goto Rollback

As this transaction is executed, how would the processing occur. From the
first update statement one row would be retrieved. Then next update
statement is processed and action occurs - mainly find where field1 = 5 and
update field2

Then control is based to first statement. Is this correct? This would mean
in this example that more processing resources would be required as
successive statements are processed. With update number 2 all previous rows
would be updated in addtion to the last update performed by update number 1?

Just trying to ensure that I understand the processing that is occuring here.

Thanks,
BT

--
BT

[Tibor Karaszi]

The queries are executed one after the other. The following query will see the data as it is at that
point in time (it will see the modifications performed by the first query).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"BobT" <Bo...@discussions.microsoft.com> wrote in message

news:F090B84A-A507-4798...@microsoft.com...