Clever Geek Handbook

Two updates with one condition

I wanted to do 2 updates together in one of the statements and I was hoping that using the updates in the transaction clause would help this. But this is not the case. So, I was expecting it to find the entry and update it. but it does the first, and when it goes to the second, the "Expired" value is already 0. How can I do this? (docid = 'Syn25331' is just a test with one record, but I want to do it for thousands of records, hence no 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;
+3


source to share


2 answers


It looks to me like you just need a comma to update both columns:



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


source


You can do it in one update statement



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


source






More articles:


All Articles