From luiz@planit.com.br Thu Dec  16 15:27:07 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 958801337B61
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Dec 2010 11:27:07 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 22390-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Dec 2010 15:27:00 +0000 (UTC)
X-Greylist: delayed 00:28:53.536453 by SQLgrey-1.7.6
Received: from andromeda.neonex.com.br (dns1.neonex.com.br [186.225.19.19])
	by mail.postgresql.org (Postfix) with ESMTP id BC9571337B49
	for <pgsql-sql@postgresql.org>; Thu, 16 Dec 2010 11:26:59 -0400 (AST)
Received: from [201.20.39.226] (helo=[127.0.0.1])
	by andromeda.neonex.com.br with esmtpa (Exim 4.72)
	(envelope-from <luiz@planit.com.br>) id 1PTFHQ-00048u-9A
	for pgsql-sql@postgresql.org; Thu, 16 Dec 2010 12:58:04 -0200
Message-ID: <4D0A28F0.3020205@planit.com.br>
Date: Thu, 16 Dec 2010 12:57:52 -0200
From: "Luiz K. Matsumura" <luiz@planit.com.br>
Organization: Plan IT Tecnologia =?ISO-8859-1?Q?Inform=E1tica_Ltda=2E?=
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; pt-BR;
	rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: UPDATE in a specific order
Content-Type: multipart/alternative;
	boundary="------------020405010007050403030000"
X-Antivirus: avast! (VPS 101216-0, 16/12/2010), Outbound message
X-Antivirus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=-5 required=5 tests=BAYES_20=-0.001,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201012/82
X-Sequence-Number: 34918

This is a multi-part message in MIME format.
--------------020405010007050403030000
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi,

I have a follow scenario:

CREATE TABLE table1 (
   id integer
, vlpr numeric(10,2)
, vlab numeric(10,2)
, vlbx  numeric(15,5)
, pct  numeric(12,8)
);

CREATE TABLE table2 (
   id integer
,  fk_table1 integer
, tpop char(2)
,  valor  numeric(15,5)
);

insert into table1  VALUES ( 1, 200 , 0 , 0 , 1 );

insert into table2  VALUES
   ( 1, 1 , 'CR'  ,   100  )
, ( 2, 1 , 'BX'  ,     15  )
, ( 3, 1 , 'AC'  ,     40  );

I need to make update of table1 with data on table2 in the order of id 
of table2

I´m trying to do an update like this:

UPDATE table1
SET  vlab = vlab + CASE WHEN tHist.tpop IN ('BX' , 'DC')
             THEN - tHist.valor
            ELSE tHist.valor
            END
    , vlbx = vlbx + CASE WHEN tHist.tpop IN ('BX', 'DC')
                         THEN tHist.valor
                    ELSE 0
                    END
    , pct = CASE WHEN tHist.tpop in ('AC',  'DC' )
                 THEN (vlpr - vlbx) / vlab
            ELSE pct
            END
FROM  ( SELECT * FROM table2 ORDER BY id  ) tHist
WHERE table1.id = tHist.fk_table1


The "FROM ( SELECT * FROM table2 ORDER BY id  ) tHist"  is a try to 
force a specific order on table2 to update table1
but this isn´t working.

There are some way to do this with a UPDATE statement ?

Thanks in advance,

-- 
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
*
*

--------------020405010007050403030000
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    Hi,<br>
    <br>
    I have a follow scenario:<br>
    <br>
    CREATE TABLE table1 (<br>
    &nbsp; id integer<br>
    , vlpr numeric(10,2)<br>
    , vlab numeric(10,2)<br>
    , vlbx&nbsp; numeric(15,5)<br>
    , pct&nbsp; numeric(12,8)<br>
    );<br>
    <br>
    CREATE TABLE table2 (<br>
    &nbsp; id integer<br>
    ,&nbsp; fk_table1 integer<br>
    , tpop char(2)<br>
    ,&nbsp;
    valor&nbsp; numeric(15,5)<br>
    );<br>
    <br>
    insert into table1&nbsp; VALUES ( 1, 200 , 0 , 0 , 1 );<br>
    <br>
    insert into table2&nbsp; VALUES <br>
    &nbsp; ( 1, 1 , 'CR'&nbsp; ,&nbsp;&nbsp; 100&nbsp; )<br>
    , ( 2, 1 , 'BX'&nbsp; ,&nbsp;&nbsp;&nbsp;&nbsp; 15&nbsp; )<br>
    , ( 3, 1 , 'AC'&nbsp; ,&nbsp;&nbsp; &nbsp; 40&nbsp; );<br>
    <br>
    I need to make update of table1 with data on table2 in the order of
    id of table2<br>
    <br>
    I&acute;m trying to do an update like this: <br>
    <br>
    UPDATE table1<br>
    SET&nbsp; vlab = vlab + CASE WHEN tHist.tpop IN ('BX' , 'DC') <br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; THEN - tHist.valor <br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; ELSE tHist.valor <br>
    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; END<br>
    &nbsp;&nbsp; , vlbx = vlbx + CASE WHEN tHist.tpop IN ('BX', 'DC') <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN tHist.valor <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE 0 <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END<br>
    &nbsp;&nbsp; , pct = CASE WHEN tHist.tpop in ('AC',&nbsp; 'DC' ) <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN (vlpr - vlbx) / vlab&nbsp; <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE pct <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END<br>
    FROM&nbsp; ( SELECT * FROM table2 ORDER BY id&nbsp; ) tHist<br>
    WHERE table1.id = tHist.fk_table1<br>
    <br>
    <br>
    The "FROM ( SELECT * FROM table2 ORDER BY id&nbsp; ) tHist"&nbsp; is a try to
    force a specific order on table2 to update table1<br>
    but this isn&acute;t working.<br>
    <br>
    There are some way to do this with a UPDATE statement ?<br>
    <br>
    Thanks in advance,<br>
    <br>
    <div class="moz-signature">-- <br>
      Luiz K. Matsumura<br>
      Plan IT Tecnologia Inform&aacute;tica Ltda.<br>
      <b><br>
      </b></div>
  </body>
</html>

--------------020405010007050403030000--



From jasen@xnet.co.nz Thu Dec  16 19:59:12 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id A6AD51337B99
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Dec 2010 15:59:12 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 24353-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu, 16 Dec 2010 19:59:05 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtpcorp.com (smtpcorp.com [67.192.120.76])
	by mail.postgresql.org (Postfix) with ESMTP id BF5E41337B98
	for <pgsql-sql@postgresql.org>; Thu, 16 Dec 2010 15:59:04 -0400 (AST)
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed;
 d=smtpcorp.com;
	s=main; h=References:Message-ID:Date:Subject:From:To;
	bh=A1SinAANjB0q+ZkRfmowbFUh/1F19Dipew9lU2BMXug=;
	b=KsYwLHxYchO00EiGXYh1YqCZHZzDDqC5FioXQbUaB87Kl5i66OfEUhfmVBpZ8jH4gXddrYeo/vO7PK5Vf5Oq01pFJLeyFPyPzbsafOYnIRiO8A0L7qJKVT66/Tt7sN92;
Received: from ip-118-90-129-120.xdsl.xnet.co.nz ([118.90.129.120] helo=gonzo)
	by smtpcorp.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.70)
	id 1PTJyg-0005us-DD; mid 144
	for pgsql-sql@postgresql.org; Thu, 16 Dec 2010 19:59:02 +0000
Received: from news by gonzo with local (Exim 4.72)
	(envelope-from <news@reversiblemaps.ath.cx>) id 1PTJyU-0003il-Kr
	for pgsql-sql@postgresql.org; Fri, 17 Dec 2010 08:58:50 +1300
To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: UPDATE in a specific order
Date: 16 Dec 2010 19:58:50 GMT
Organization: Dis (not Dat) Organisation
Lines: 30
Message-ID: <iedr1q$dv1$1@reversiblemaps.ath.cx>
References: <4D0A28F0.3020205@planit.com.br>
NNTP-Posting-Host: localhost
X-Trace: reversiblemaps.ath.cx 1292529530 14305 127.0.0.1 (16 Dec 2010
	19:58:50 GMT)
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a
	+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;
	(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6
	{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;
	8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent: slrn/pre1.0.0-18 (Linux)
X-Smtpcorp-Track: 18584165.1.57647584
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201012/83
X-Sequence-Number: 34919


> I need to make update of table1 with data on table2 in the order of id
> of table2

that looks like EAV. is it?

> I=B4m trying to do an update like this:

that's not going to work.

perhaps you can rewrite the from part to only return one row for every
table1_fk,  this one row will combine several rows from table2

> The "FROM ( SELECT * FROM table2 ORDER BY id  ) tHist"  is a try to
> force a specific order on table2 to update table1
> but this isn=B4t working.

will only work if the optimiser picks index join on table 1

> There are some way to do this with a UPDATE statement ?

to do it with an update statement you need no more than one rows in the
from for each row in the target.

easiest non update statement approach is probably to use a plpgsql 
function with a loop.

basically you need to find another way to do it.





From luiz@planit.com.br Fri Dec  17 19:02:10 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 9AE08133616B
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 17 Dec 2010 15:02:10 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 58404-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 17 Dec 2010 19:02:03 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from andromeda.neonex.com.br (dns1.neonex.com.br [186.225.19.19])
	by mail.postgresql.org (Postfix) with ESMTP id C845A133655C
	for <pgsql-sql@postgresql.org>; Fri, 17 Dec 2010 15:02:02 -0400 (AST)
Received: from [201.20.39.226] (helo=[127.0.0.1])
	by andromeda.neonex.com.br with esmtpa (Exim 4.72)
	(envelope-from <luiz@planit.com.br>)
	id 1PTfYy-0003Ki-V5; Fri, 17 Dec 2010 17:01:57 -0200
Message-ID: <4D0BB39A.9040204@planit.com.br>
Date: Fri, 17 Dec 2010 17:01:46 -0200
From: "Luiz K. Matsumura" <luiz@planit.com.br>
Organization: Plan IT Tecnologia =?ISO-8859-1?Q?Inform=E1tica_Ltda=2E?=
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; pt-BR;
	rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, jasen@xnet.co.nz
Subject: Re: UPDATE in a specific order
References: <4D0A28F0.3020205@planit.com.br>
	<iedr1q$dv1$1@reversiblemaps.ath.cx>
In-Reply-To: <iedr1q$dv1$1@reversiblemaps.ath.cx>
Content-Type: multipart/alternative;
	boundary="------------050702020909050108090001"
X-Antivirus: avast! (VPS 101217-0, 17/12/2010), Outbound message
X-Antivirus-Status: Clean
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201012/87
X-Sequence-Number: 34923

This is a multi-part message in MIME format.
--------------050702020909050108090001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Thanks  for reply,

Em 16/12/2010 17:58, Jasen Betts escreveu:
>> I need to make update of table1 with data on table2 in the order of id
>> of table2
> that looks like EAV. is it?
>

Err, I don´t know so much about EAV, so I think that isn´t. I´m just 
trying to reproduce a calc in a spreeadsheet.
>> There are some way to do this with a UPDATE statement ?
> to do it with an update statement you need no more than one rows in the
> from for each row in the target.
>
> easiest non update statement approach is probably to use a plpgsql
> function with a loop.
>
> basically you need to find another way to do it.
>

Ok! I make a function in plpgsql to do it.

Thank´s again !

*
*

--------------050702020909050108090001
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    Thanks&nbsp; for reply,<br>
    <br>
    Em 16/12/2010 17:58, Jasen Betts escreveu:
    <blockquote cite="mid:iedr1q$dv1$1@reversiblemaps.ath.cx"
      type="cite">
      <blockquote type="cite">
        <pre wrap="">I need to make update of table1 with data on table2 in the order of id
of table2
</pre>
      </blockquote>
      <pre wrap="">
that looks like EAV. is it?

</pre>
    </blockquote>
    <br>
    Err, I don&acute;t know so much about EAV, so I think that isn&acute;t. I&acute;m just
    trying to reproduce a calc in a spreeadsheet.
    <blockquote cite="mid:iedr1q$dv1$1@reversiblemaps.ath.cx"
      type="cite">
      <blockquote type="cite">
        <pre wrap="">There are some way to do this with a UPDATE statement ?
</pre>
      </blockquote>
      <pre wrap="">
to do it with an update statement you need no more than one rows in the
from for each row in the target.

easiest non update statement approach is probably to use a plpgsql 
function with a loop.

basically you need to find another way to do it.

</pre>
    </blockquote>
    <br>
    Ok! I make a function in plpgsql to do it.<br>
    <br>
    Thank&acute;s again !<br>
    <div class="moz-signature"><br>
      <b><br>
      </b></div>
  </body>
</html>

--------------050702020909050108090001--