DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=GNuAafmk2nQiE1yEv5fa7qSihQE+Dd40LOGdJmIG4ufzuZ5/WBwhzVktWaKfKh3Fyg
	c72xlGtbHWZ9kdnXOOSTX9y332dXIELnGIbvg9rDHfOmrIEXzKR0gtJc7esYscHb0hYX
	TrVs6ykF4vsAZKC59HlvisoezWYZdmpNkrV3k=
MIME-Version: 1.0
In-Reply-To: <4A56F6D2.7070802@gmx.net>
References: <4A56F6D2.7070802@gmx.net>
Date: Fri, 10 Jul 2009 10:21:55 +0200
Message-ID: <162867790907100121x4437c0ddj63d14524768ffc56@mail.gmail.com>
Subject: Re: How update a table within a join efficiently ?
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

2009/7/10 Andreas <maps.on@gmx.net>:
> Hi,
> how would I update a table within a join in a more efficient way?
>
> E.g. the folowing case:
> table_a holds abstract elements. One column represents "priority" which c=
an
> be based on information of other tables.
> table_b might hold such details in a column "size" for about 3000 of 8000=
0
> records out of table_a.
>
> I'd like to do this:
> UPDATE table_a
> SET table_a.prio =3D CASE WHEN size >=3D 10 THEN 1 ELSE 2 END
> FROM table_a JOIN table_b USING (table_a_id)

hello

don't repeat target table in FROM clause

UPDATE table_a
 SET table_a.prio =3D CASE WHEN size >=3D 10 THEN 1 ELSE 2 END
 FROM table_b WHERE table_a.table_a_id =3D table_b.table_a_id;

regards
Pavel Stehule


>
> This doesn't work.
> But the folowing does, though it looks not efficient with those 3000 SELE=
CTs
> instead of one preparing JOIN that fetches the relevant info. =C2=A0:(
>
> UPDATE table_a
> SET prio =3D
> (
> SELECT CASE WHEN size >=3D 10 THEN 1 ELSE 2 END
> FROM table_b
> WHERE table_a.table_a_id =3D table_b.table_a_id
> )
> WHERE table_a_id IN (SELECT table_a_id FROM table_b);
>
> Is there a better way?
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>