Обсуждение: 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

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
>

Andrew Bell <acbell@iastate.edu> writes:
> 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.

You just do it:

UPDATE table1 SET netSalary = table1.salary - table2.deductions
    WHERE table1.employee = table2.employee;

If you want to be slightly clearer you can do

UPDATE table1 SET netSalary = table1.salary - table2.deductions
    FROM table2
    WHERE table1.employee = table2.employee;

so that it's obvious there's a join going on.  But the first will give
you an implicit "FROM table2" anyway.

AFAICT neither of these is legal per SQL92, but I think it's a common
extension.  If you wanted to be pure spec-conformant you'd have to write
something like

UPDATE table1 SET
    netSalary = salary - (SELECT deductions FROM table2
                          WHERE table1.employee = table2.employee);

but this is not any more readable IMHO, and it'll likely be slower
(at least in Postgres, which isn't super smart about rewriting
sub-selects as joins).

            regards, tom lane

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

At 15:12 23/11/01, you wrote:
>Last I checked, you cannot perform arithmetic functions inside a query.  I
>suggest retrieving the values, do the math, then update.


You can perform all sorts of arithmetic operations inside a query in
Postgres, although sometimes you ave to be a bit clever with the syntax.
(I'm not sure how much of it is strict SQL92 though.) It would be a
nightmare working with datetimes if you couldn't subtract them from each
other, add intervals, etc.

best,

Mo



Mo Holkar
Digital Mind Games -- log on to take over
mo.holkar@digitalmindgames.com
http://www.digitalmindgames.com