CODEWITHSUNDEEP
sqlsql-server
Rainmaker
Rainmaker

Reputation: 321

Two updates with one condition

I wanted to do 2 updates together in one statements and I was hoping using the updates within a transaction clause will help this. But it did not. So below, I was expecting it to find the record and update it. but it does the first one and when it goes to the second that the value "Expired" is 0 already. How can I do this? (docid ='Syn25331' is just to test with one record. But I want to do it for thousands of record, hence without the docid ='Syn25331' condition)

BEGIN TRANSACTION;

Update main
Set expired = 0
where Expires > GETDATE() and Expired =1 and docid ='Syn25331'


Update main
Set TractorID = AssetID
where Expires > GETDATE() and Expired =1 and docid ='Syn25331'

COMMIT;

Upvotes: 3

Views: 76

Answers (2)

Jacob H
Jacob H

Reputation: 2515

I think you just need a comma here to update both columns:

Update main
Set expired = 0, TractorID = AssetID
where Expires > GETDATE() and Expired = 1

Upvotes: 6

Stefano Zanini
Stefano Zanini

Reputation: 5926

You can do that in a single update statement

UPDATE main
SET    expired = 0,
       TractorID = AssetID
WHERE  Expires > GETDATE() AND
       Expired = 1

Upvotes: 2

Related Questions