DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=a0lXd3ROPm57tCqLnhR5OBW4+FVWd4hjd+IZzaeZybBTmQpx5wmodbBjjWTZzIDaXl+/PPpWgbHwsvWGp9sky6WNMD6zU8wq7HQn5Eoj2Eu0xjPrxkDSd6bGsX14XLYQolUkavTbkYSJKJ7/XVRGExwhRfsD+sa3t2FB8b7jquU=
Message-ID: <690707f60804021954x6d4f19aas22de2def30f5c1de@mail.gmail.com>
Date: Wed, 2 Apr 2008 23:54:18 -0300
From: "Osvaldo Kussama" <osvaldo.kussama@gmail.com>
To: "Ivan Sergio Borgonovo" <mail@webthatworks.it>
Subject: Re: update with join
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20080402224753.78f454fc@webthatworks.it>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20080402224753.78f454fc@webthatworks.it>

2008/4/2, Ivan Sergio Borgonovo <mail@webthatworks.it>:
> I've
>
>  create table types(
>   typeid int,
>   special boolean not null
>  );
>
>  create table methods(
>   methodid int,
>   typeid references types(typeid),
>  );
>
>  create table orders(
>   orderid int
>  );
>
>  create table order_payments(
>   payid int
>   orderid references order(orderid),
>   methodid references method(methodid),
>   issued boolean not null default false
>  );
>
>  orderid payid methodid special
>  1       1     1        t
>  1       2     2        t
>  1       3     3        t
>  1       4     4        f
>  1       5     4        f
>
>  I'd like to chose one payid
>  If the payid is "special" just set issued to true for that payid,
>  leave the other unchanged.
>  If the payid is not "special" set issued for all the payid in the
>  same order.
>
>  eg.
>  So if payid=4 I'd have
>
>  orderid payid methodid special issued
>  1       1     1        t       t
>  1       2     2        t       t
>  1       3     3        t       t
>  1       4     4        f       t
>  1       5     4        f       t
>
>  and if payid=2
>
>  orderid payid methodid special issued
>  1       1     1        t       f
>  1       2     2        t       t
>  1       3     3        t       f
>  1       4     4        f       f
>  1       5     4        f       f
>
>  This stuff below doesn't work:
>
>  update order_payments
>   set issued=true where payid in (
>     select p.payid
>       from order_payments p
>       join methods as m on m.methodid=p.methodid
>       join types as t on m.typeid=t.typeid
>     where (p.orderid=%d and not t.special) or p.payid=%d);
>
>  and I can understand why but I can't rewrite it to make it work.
>


Try:
UPDATE order_payments
 SET issued=true FROM methods m, types t
 WHERE m.methodid=p.methodid AND
       m.typeid=t.typeid AND
       ((order_payments.orderid=%d AND NOT t.special) OR
         order_payments.payid=%d));

Osvaldo