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
booleannot 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
onm.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