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

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 (view raw or flat)
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

pgsql-novice by date

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

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