Date: Wed, 2 Apr 2008 22:47:53 +0200
From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: update with join
Message-ID: <20080402224753.78f454fc@webthatworks.it>
Organization: www.WebThatWorks.it
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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.

thanks


-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it