Re: Using case in an update statement ?

On Tue, 4 Jun 2002 16:04:59 +0200, "Peter Alberer" <peter(at)alberer(dot)com>
wrote:
>Hi,
>
>I would like to change some rows in a table to two different values
>depending on the current value.
>The value of the column "status" should be changed to ''CLOSED_SUCC''
>when its current value is ''OPEN_SUCC'' and it should be ''CLOSED_FAIL''
>when the current value is ''OPEN_FAIL''. Of course I could write two
>statements, but can it be done in one statement ?
>
>I tried to do it with "case" but that failed.
>
>update lr_object_usage set status = (case when status = ''OPEN_SUCC''
>then ''CLOSED_SUCC'' when status = ''OPEN_FAIL'' then ''CLOSED_FAIL'');
Peter,

the error message would be of great help for those trying to help you.
If you are entering your UPDATE into psql, you get
ERROR: parser: parse error at or near "OPEN_SUCC"
because there are too many quotes.

If your statement is part of a procedure, you get
ERROR: parser: parse error at or near ")"
because CASE has to be terminated by END.

UPDATE lr_object_usage
SET status = (CASE status
WHEN 'OPEN_SUCC' THEN ''CLOSED_SUCC''
WHEN 'OPEN_FAIL' THEN 'CLOSED_FAIL'
END);

You might want to add
WHERE status = 'OPEN_SUCC' OR status = 'OPEN_FAIL'
otherwise you set status = NULL in all the other rows.

Servus
Manfred