pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcase when... end in update clause?
6+ messages / 2 participants
[nested] [flat]
* case when... end in update clause?
@ 2008-03-12 15:47 Emi Lu <emilu@encs.concordia.ca>
0 siblings, 2 replies; 6+ messages in thread
From: Emi Lu @ 2008-03-12 15:47 UTC (permalink / raw)
To: pgsql-sql
Hello,
May I know can "case when " used by update clause. If yes, how?
I use one small Example, table: test
=============================
id
==
5
6
8
try to update test.id
update test
case
when id =5 then SET id = 6
end
;
Thanks!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: case when... end in update clause?
@ 2008-03-12 16:01 Emi Lu <emilu@encs.concordia.ca>
parent: Emi Lu <emilu@encs.concordia.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Emi Lu @ 2008-03-12 16:01 UTC (permalink / raw)
To: Aaron Bono <aaron.bono@aranya.com>; +Cc: pgsql-sql
Aaron Bono wrote:
> On Wed, Mar 12, 2008 at 10:47 AM, Emi Lu <emilu@encs.concordia.ca> wrote:
>
>> Hello,
>>
>> May I know can "case when " used by update clause. If yes, how?
>>
>> I use one small Example, table: test
>> =============================
>> id
>> ==
>> 5
>> 6
>> 8
>>
>> try to update test.id
>>
>>
>> update test
>>
>> case
>> when id =5 then SET id = 6
>> end
>> ;
>>
>> <http://www.postgresql.org/mailpref/pgsql-sql;
>>
>
> Is this what you are looking for:
>
>
> update test
> set id = case when id = 5 then 6 else id end;
>
Exactly what I am looking for!
Thanks a lot!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: case when... end in update clause?
@ 2008-03-12 16:01 Scott Marlowe <scott.marlowe@gmail.com>
parent: Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2008-03-12 16:01 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
On Wed, Mar 12, 2008 at 8:47 AM, Emi Lu <emilu@encs.concordia.ca> wrote:
> Hello,
>
> May I know can "case when " used by update clause. If yes, how?
>
> I use one small Example, table: test
> =============================
> id
> ==
> 5
> 6
> 8
>
> try to update test.id
>
>
> update test
>
> case
> when id =5 then SET id = 6
> end
> ;
would this work:
update test set id=5 where id=6;
???
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: case when... end in update clause?
@ 2008-03-12 16:11 Emi Lu <emilu@encs.concordia.ca>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Emi Lu @ 2008-03-12 16:11 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
>>
>> I use one small Example, table: test
>> =============================
>> id
>> ==
>> 5
>> 6
>> 8
>>
>> try to update test.id
>>
>>
>> update test
>>
>> case
>> when id =5 then SET id = 6
>> end
>> ;
>
> would this work:
>
> update test set id=5 where id=6;
>
No. I provide one small fake example.
I want to know how to use case when in update/set clause as the following:
update test
set id = case when id = 5 then 6 else id end;
Thanks.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: case when... end in update clause?
@ 2008-03-12 16:19 Scott Marlowe <scott.marlowe@gmail.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2008-03-12 16:19 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
On Wed, Mar 12, 2008 at 9:11 AM, Emi Lu <emilu@encs.concordia.ca> wrote:
> >>
> >> I use one small Example, table: test
> >> =============================
> >> id
> >> ==
> >> 5
> >> 6
> >> 8
> >>
> >> try to update test.id
> >>
> >>
> >> update test
> >>
> >> case
> >> when id =5 then SET id = 6
> >> end
> >> ;
> >
> > would this work:
> >
> > update test set id=5 where id=6;
> >
>
> No. I provide one small fake example.
>
>
> I want to know how to use case when in update/set clause as the following:
>
> update test
> set id = case when id = 5 then 6 else id end;
Well, I think my point stands, that this stuff really belongs in a
where clause. The way you're doing it it updates ALL the rows whether
it needs to or not, my way only updates the rows that need it. How
about a REAL example of what you're trying to do. There may well be a
more efficient way of doing this than using a case statement. Or
not...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: case when... end in update clause?
@ 2008-03-12 16:25 Emi Lu <emilu@encs.concordia.ca>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Emi Lu @ 2008-03-12 16:25 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
Hi Scott ,
>> No. I provide one small fake example.
>>
>>
>> I want to know how to use case when in update/set clause as the following:
>>
>> update test
>> set id = case when id = 5 then 6 else id end;
>
> Well, I think my point stands, that this stuff really belongs in a
> where clause. The way you're doing it it updates ALL the rows whether
> it needs to or not, my way only updates the rows that need it. How
> about a REAL example of what you're trying to do. There may well be a
> more efficient way of doing this than using a case statement. Or
> not...
Ok.
The situation would like this, in one query:
UPDATE tableName
SET
col1 = val1 when col1 satisfy condition1
col1 = val2 when col1 satisfy condition2
... ...
col1 = valN when col1 satisfy conditionN
... ...
WHERE
col3 satisfy conditionX;
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-03-12 16:25 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-12 15:47 case when... end in update clause? Emi Lu <emilu@encs.concordia.ca>
2008-03-12 16:01 ` Emi Lu <emilu@encs.concordia.ca>
2008-03-12 16:01 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-12 16:11 ` Emi Lu <emilu@encs.concordia.ca>
2008-03-12 16:19 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-12 16:25 ` Emi Lu <emilu@encs.concordia.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox