[postgis-users] query error when combining UPDATE, CASE and WHERE

[Mark Cave-Ayland]

On 31/12/10 17:23, zhang zhe wrote:

> Hello,
>
> I try to update one column value based the other two column values´s
> conditions. This code works
>
> select book, case when table1.price>200 and table1.price<400 then
> '40'else book end from table 1 where table1.catalog='112';
>
> Now I want to update the table1, so the book value will change into 40
> when the other two conditions are true.
>
> update book, case when table1.price>200 and table1.price<400 then '40'
> else book end from table 1 where table1.catalog='112';
>
> It gives error. Can anyone help me?

You probably want something along the lines of this (untested so please 
check it first):

update table1 set book = (select case when price > 200 and price < 400 
then '40' else book end) where catalog = '112';


HTH,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs