Re: Update from a table.

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@iastate.edu>
>To: <pgsql-novice@postgresql.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@iastate.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@postgresql.org)
>