Re: update with join

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_paymentsSET issued=true FROM methods m, types tWHERE m.methodid=p.methodid AND      m.typeid=t.typeid AND
   ((order_payments.orderid=%d AND NOT t.special) OR        order_payments.payid=%d));
 

Osvaldo