0
update PD set quantity =
  (
    case
      when ((quantity - @transferqnty) <= 0) then attrited = 'true'
      else (quantity - @transeferqnty)
    end
  )
from K_RT_PurchaseDet as PD
inner join K_RT_Productdetails as PS on PD.product = PS.sno 
where PS.productname = @Purchaseid

I want to update column name when quantity becomes zero I wrote like this but I am getting

"Incorrect syntax near '='."

What's wrong in this please help me....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
santhosha
  • 377
  • 1
  • 6
  • 28
  • 1
    In T-SQL, the `CASE` is an **expression** - it can **return a value** - but it **CANNOT** execute code inside itself! It's **not** like a `switch { case. ..}` statement in C# or VB.NET – marc_s Jan 27 '14 at 05:58

1 Answers1

0

You can't have assignment operation inside a case statement, it can only return value which can be assigned to quantity in your above code. So replace attrited = 'true' with whatever value you want to assign to quantity. It will be something like below.

update PD set quantity =
  (
    case
      when ((quantity - @transferqnty) <= 0) then quantity
      else (quantity - @transeferqnty)
    end
  )
from K_RT_PurchaseDet as PD
inner join K_RT_Productdetails as PS on PD.product = PS.sno 
where PS.productname = @Purchaseid

In the above i am maintaning the same value for quantity.

If you are looking to assign different column based on condition, you can't change the column name dynamically. But you can try the solution mentioned in this LINK.

Community
  • 1
  • 1
Naveen
  • 1,496
  • 1
  • 15
  • 24
  • but I want to update row when quantity becomes Zero how can I write for that? – santhosha Jan 27 '14 at 06:24
  • You can check for any number of condition inside CASE, using multiple WHEN. The above my query will assign (quantity - @transeferqnty) only if ((quantity - @transferqnty) <= 0) satisfied else row will have the same value. Just update the query as your requirement. – Naveen Jan 27 '14 at 06:26
  • Are you looking to assign multiple column based on condition? If so i have already gave you a link. That is the way you have to do it, if you find any difficulty, you are welcome. – Naveen Jan 27 '14 at 06:32