CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
user3946530
user3946530

Reputation:

Postgres syntax error in if condition

I am trying to calculate commission and according to that commission value (commiss) i want to update a column value fixed_fee

 select  st1.* ,(st1.order_item_value * st1.commission_rate)/100 commiss , 
 if commiss < 50 then 
   update payment_error set fixed_fee = -5 where commiss <= 50
 else if commiss >= 50 then
     update payment_error set fixed_fee = -10 where commiss >=50
 else
   end if
 from payment_error as st1 join payment_error as st2 on st1.order_item_id=st2.order_item_id ;

while running is then error come is :

 ERROR:  syntax error at or near "<"
 LINE 2: if commiss<50 then 
              ^

   ********** Error **********

   ERROR: syntax error at or near "<"
   SQL state: 42601
    Character: 87

Upvotes: 1

Views: 516

Answers (2)

user330315
user330315

Reputation:

You can't use if in SQL. This is only available in procedural languages like PL/pgSQL.

But more importantly you cannot embed an update statement inside a select statement like that.

What you are trying to do can be done with a single UPDATE statement using a CASE:

update payment_error 
   set fixed_fee = case 
                     when (order_item_value * commission_rate)/100 <= 50 then -5
                     else 5 --<<< different value here
                   end;

You are setting the fixed_fee to -5 in both cases - I assume that is a typo, and you do mean to assign a different fee for the case when the comission is greater than 50

Upvotes: 6

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

You cannot use update and select together. To update you can try this:

update payment_error 
set fixed_fee = case when (order_item_value * commission_rate * .01) < 50 then -5
else 5  --In your case you have use both as -5 so change it as per your reqm

Upvotes: 0

Related Questions