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