Re: Update from a table.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrew Bell <acbell(at)iastate(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Update from a table.
Date: 2001-11-23 17:03:01
Message-ID: 6464.1006534981@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Andrew Bell <acbell(at)iastate(dot)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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Frank Bax 2001-11-23 17:22:52 Re: Update from a table.
Previous Message Tom Lane 2001-11-23 16:43:43 Re: [HACKERS] upper and lower doesn't work with german umlaut?