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

On Wed, Mar 12, 2008 at 9:11 AM, Emi Lu <emilu(at)encs(dot)concordia(dot)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...