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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
