Re: update with join - Mailing list 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
updateshop_commerce_ordergroup_pay   set Issued=true where PayID=_PayID; else  update shop_commerce_ordergroup_pay
setIssued=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