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
Have you tested the query? Does it yield the expected/desired results?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
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...
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
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, 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...