Re: case when... end in update clause?

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...