DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=sFepiwTzrtx2UyEAvtb7VoCfmj9LjNYuXKDNN3+KW0PYrKquN6zQR5YREnxsvEEDSL+PV/tP/Gry+Sx4Z7BzKgUbyblHBQlUVrducoqrFn5oE6heHrH2MCWr+8obDfgshO1/ohv9Ll8Qs4DeyMkuEBKHxs7ECceHeyDO7gvLCw4=
Message-ID: <dcc563d10803120919w5d26ff5amb7c0cf10cfcb99df@mail.gmail.com>
Date: Wed, 12 Mar 2008 09:19:15 -0700
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: emilu@encs.concordia.ca
Subject: Re: case when... end in update clause?
Cc: pgsql-sql@postgresql.org
In-Reply-To: <47D8009D.6000703@encs.concordia.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <47D7FB06.10707@encs.concordia.ca>
	<dcc563d10803120901j5f6bd4as8eae1320318b7060@mail.gmail.com>
	<47D8009D.6000703@encs.concordia.ca>

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