From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: update with join
Date: Wed, 2 Apr 2008 22:47:53 +0200
Message-ID: <20080402224753.78f454fc@webthatworks.it> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@webthatworks.it
Subject: Re: update with join
In-Reply-To: <20080402224753.78f454fc@webthatworks.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox