Skip site navigation (1) Skip section navigation (2)

Re: Update from a table.

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: "Mark G(dot) Franz" <mgfranz(at)pe(dot)net>
Cc: <pgsql-novice(at)postgresql(dot)org>, "Andrew Bell" <acbell(at)iastate(dot)edu>
Subject: Re: Update from a table.
Date: 2001-11-23 17:22:52
Message-ID: 3.0.6.32.20011123122252.0093fa40@pop6.sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
No arithmetic?  Really?

create table t1 (emp int, salary int, netsal int);
create table t2 (emp int, dedn int);
insert into t1 values( 1, 100);
insert into t1 values( 2, 200);
insert into t2 values( 1, 5);
insert into t2 values( 1, 7);
insert into t2 values( 2, 8);
insert into t2 values( 2, 15);
insert into t2 values( 2, 19);

update t1 set netsal = salary - (select sum(dedn) from t2 where t1.emp =
t2.emp);

select * from t1;

 emp | salary | netsal
-----+--------+--------
   1 |    100 |     88
   2 |    200 |    158
(2 rows)

select version();
                              version
-------------------------------------------------------------------
 PostgreSQL 7.1 on i386-unknown-openbsd2.9, compiled by GCC 2.95.3
(1 row)

At 07:12 AM 11/23/01 -0800, Mark G. Franz wrote:
>Last I checked, you cannot perform arithmetic functions inside a query.  I
>suggest retrieving the values, do the math, then update.
>
>Mark
>----- Original Message -----
>From: "Andrew Bell" <acbell(at)iastate(dot)edu>
>To: <pgsql-novice(at)postgresql(dot)org>
>Sent: 21 November, 2001 6:02 AM
>Subject: [NOVICE] Update from a table.
>
>
>> Hi,
>>
>> I want to update one table from another table based on a criteria.  For
>> instance:
>>
>>             table1                           table2
>> employee | salary | netSalary         employee | deductions
>>
>> I want to update table1.netSalary to be table1.salary - table2.deductions
>where
>>    table1.employee = table2.employee.
>>
>> I don't see any way to do something like this with the syntax.  What am I
>> missing, or what can be recommended?
>>
>>
>> -- Andrew Bell
>> acbell(at)iastate(dot)edu
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/users-lounge/docs/faq.html
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

In response to

pgsql-novice by date

Next:From: Mo HolkarDate: 2001-11-23 17:36:37
Subject: Re: Update from a table.
Previous:From: Tom LaneDate: 2001-11-23 17:03:01
Subject: Re: Update from a table.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group