[postgis-users] postgres case(conditional)statement problem

[Nicklas Avén]

Hallo

I think what you want to do is something like:

SELECT price,
CASE WHEN 200<selling1.price AND selling1.price <300 THEN '400'
ELSE price
END
FROM selling1
WHERE selling1.item = '1';


The case expression is used in the SELECT-statement
see the doc: http://www.postgresql.org/docs/9.0/interactive/functions-conditional.html#AEN15488


I guess you can just put the case expression in the update-query the same way,
but if there is just one case where you want to change the value, the update query could look just like this:

UPDATE selling1 set price='400'
WHERE price>200 and price < 300;

HTH
Nicklas



On Sat, 2010-12-04 at 07:45 -0500, zhang zhe wrote:
> Hello,
> 
> Does anyone familiar with postgres case statement. 
> 
> I have a tale called selling1.sql, inside table there are two columns
> item(1,1,2,3,3,3,4,4,4....)  and price(234, 243, 245, 342, 124, 245,
> 343, 123,455.....). I want to select the price where item id is equal
> to 1, and if its price is bigger than 200 and smaller than 300, the
> price will be changed into 400 . so in this case, price column value
> will be changed into,  price(400,400, 245,342,124, 245, 343, 123,
> 455...)
> 
> My sql statement is like this
> 
> select price from selling1 where selling1.item = '1',
>  case when 200<selling1.price and selling1.price <300 then '400'else
> 'other' END;
> 
> but it always give me the error. It said syntax error in case. 
> 
> Do you known whatś wrong? 
> 
> Thanks
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users