pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate with join
3+ messages / 2 participants
[nested] [flat]
* update with join
@ 2008-04-02 20:47 Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-04-03 02:54 ` Re: update with join Osvaldo Kussama <osvaldo.kussama@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-04-02 20:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: update with join
2008-04-02 20:47 update with join Ivan Sergio Borgonovo <mail@webthatworks.it>
@ 2008-04-03 02:54 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
2008-04-03 08:53 ` Re: update with join Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 1 reply; 3+ messages in thread
From: Osvaldo Kussama @ 2008-04-03 02:54 UTC (permalink / raw)
To: Ivan Sergio Borgonovo <mail@webthatworks.it>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: update with join
2008-04-02 20:47 update with join Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-04-03 02:54 ` Re: update with join Osvaldo Kussama <osvaldo.kussama@gmail.com>
@ 2008-04-03 08:53 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 0 replies; 3+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-04-03 08:53 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-04-03 08:53 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-02 20:47 update with join Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-04-03 02:54 ` Osvaldo Kussama <osvaldo.kussama@gmail.com>
2008-04-03 08:53 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox