CODEWITHSUNDEEP
sqlsql-serversql-server-2008
qinking126
qinking126

Reputation: 11875

sql server 2008, need help to write conditional update statement

I have a simple update statment.

Update tbABC
Set salary = 1000, name = 'mike'
Where Id = 1

I need to add a condition when update salary, if salary = 0, then change to 1000, otherwise salary will not change.

I did my research, and found a similar question. using a conditional update statement in sql

Update tbABC
Set salary = CASE WHEN (salary = 0) then 1000 ELSE ??????? END, 
    name = 'mike'
Where Id = 1

I got stuck on that ???? part. now sure what to put there to make it salary = salary.

Upvotes: 1

Views: 1226

Answers (2)

Thomas W
Thomas W

Reputation: 14164

Unless absolutely necessary, I'd probably prefer using the WHERE clause rather than a complicated CASE function. Simplifying, this would give:

update tbABC set salary=1000, name='mike'   -- using condition for both field updates
where Id=1 and salary=0;

Or preserving the exact logic over the transaction:

update tbABC set salary=1000      -- by ID & only if second condition met
where Id=1 and salary=0;

update tbABC set name='mike'      -- by ID.
where Id=1;

I don't really believe there's a real-world case for updating Employee's name unconditionally, but having some condition on his Salary update.

Upvotes: 2

frkn
frkn

Reputation: 324

this should work

Update tbABC
Set salary = CASE WHEN (salary = 0) then 1000 ELSE salary END, 
name = 'mike'
Where Id = 1

Upvotes: 2

Related Questions