Re: update with join

2008/4/2, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)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