Re: update with join

On Wed, 2 Apr 2008 23:54:18 -0300
"Osvaldo Kussama" <osvaldo(dot)kussama(at)gmail(dot)com> wrote:

> 2008/4/2, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)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