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

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

Osvaldo

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Wed, 2 Apr 2008 23:54:18 -0300
"Osvaldo Kussama" <osvaldo.kussama@gmail.com> wrote:

> 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_payments
> SET issued=true FROM methods m, types t
> WHERE m.methodid=p.methodid AND

p -> order_payments

> m.typeid=t.typeid AND
> ((order_payments.orderid=%d AND NOT t.special) OR
> order_payments.payid=%d));

one less )

Even after correcting the few typos this version obtain the same
result of
update order_payments set issued=true where payid=%d

I ended up in writing a plpgsql function that retrieve special and
then have an if block.

create or replace function IssuePay(_PayID int,
out _OrderGroupID bigint, out _Online boolean)
as
$$
begin
select into _OrderGroupID, _OnLine p.OrderGroupID, t.OnLine
from shop_commerce_ordergroup_pay p
join shop_commerce_paymethods m on p.PayMethodID=m.MethodID
join shop_commerce_paytypes t on m.TypeID=t.TypeID
where PayID=_PayID;
if(_OnLine) then
update shop_commerce_ordergroup_pay
set Issued=true where PayID=_PayID;
else
update shop_commerce_ordergroup_pay
set Issued=true where OrderGroupID=_OrderGroupID;
end if;
return;
end;
$$ language plpgsql;

mutatis mutandis.

It may not be the most elegant thing but it is enough encapsulated it
won't be a pain to refactor once I become a better DBA or someone
else point out a better solution on the list.
I'd be curious if it had a performance penalty over a one update
statement.

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