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--