CODEWITHSUNDEEP
sql-servert-sql
trx
trx

Reputation: 2157

Two update on a single statement

I have two update statements that we run on SQL server they are like

UPDATE dbo.c_Account
SET Ct = Ct-1
Where 
Acc = 'IT' OR Acc = 'gt'

UPDATE dbo.c_Account
SET Ct = Ct-3
Where 
Acc = 'ABC'

Is there a possibility of doing the both update on one single statement

Upvotes: 1

Views: 98

Answers (3)

Yashveer Singh
Yashveer Singh

Reputation: 1977

     UPDATE dbo.c_Account
     SET Ct  = case Acc when 'IT' then  (Ct-1 )
     when 'GT' then ct-1
     when 'abc' then  ct-3
     else ct
     end

  WHERE t.Acc IN ('IT','gt','ABC')

Upvotes: 1

user7415753
user7415753

Reputation: 198

UPDATE dbo.c_Account
SET Ct = case
when (Acc = 'IT' OR Acc = 'gt') then Ct-1
When Acc = 'ABC' then Ct-3
Else ct
End

Above will update all records with default not changing. You could add a where statment to only update the ones you need e.g. Where acc in ('IT','gt','ABC')

Upvotes: 0

Cee McSharpface
Cee McSharpface

Reputation: 8726

You could combine it like this:

UPDATE t SET
    Ct = Ct - CASE WHEN t.Acc IN ('IT','gt') THEN 1 ELSE 3 END
FROM dbo.c_Account t
WHERE t.Acc IN ('IT','gt','ABC')

There is little benefit, except that it now will run as one atomic operation, and does not require an outer transaction.

Upvotes: 8

Related Questions