From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | "Peter Alberer" <peter(at)alberer(dot)com> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Using case in an update statement ? |
Date: | 2002-06-05 16:30:08 |
Message-ID: | sudsfucsncr55j8q6a7lregg3vcsi08nvl@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Ansley | 2002-06-05 21:09:32 | Outputting to file |
Previous Message | Josh Berkus | 2002-06-05 15:28:28 | Re: Using case in an update statement ? |