Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using case in an update statement ?

Using case in an update statement ?

From
"Peter Alberer"
Date:

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'');

Re: Using case in an update statement ?

From
"Josh Berkus"
Date:
Peter,

> update lr_object_usage set status = (case when status = ''OPEN_SUCC''
> then ''CLOSED_SUCC'' when status = ''OPEN_FAIL'' then
> ''CLOSED_FAIL'');

You're missing something:

update lr_object_usage set status = (case when status = ''OPEN_SUCC''
then ''CLOSED_SUCC'' when status = ''OPEN_FAIL'' then
''CLOSED_FAIL'' END);

FYI, in the future, please e-mail the error you get as well as your
question.  It helps us diagnose the problem.

-Josh Berkus

Re: Using case in an update statement ?

From
Manfred Koizar
Date:
On Tue, 4 Jun 2002 16:04:59 +0200, "Peter Alberer" <peter@alberer.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