Date: Thu, 3 Apr 2008 10:53:36 +0200
From: Ivan Sergio Borgonovo <mail@webthatworks.it>
To: pgsql-sql@postgresql.org
Subject: Re: update with join
Message-ID: <20080403105336.2ca72046@webthatworks.it>
In-Reply-To: <690707f60804021954x6d4f19aas22de2def30f5c1de@mail.gmail.com>
References: <20080402224753.78f454fc@webthatworks.it>
	<690707f60804021954x6d4f19aas22de2def30f5c1de@mail.gmail.com>
Organization: www.WebThatWorks.it
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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