Thread: case when... end in update clause?
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!
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!
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; ???
>> >> 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.
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...
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;