Update using an innerjoin 1

[szaunbr]

Hello, I am new to sql and am trying to do an update statement using an innerjoin.

I am in my payroll database and need to update a field in the deduction table, but need to only update one group of people, and the group id is only in the employee master table. The common "thread" is the employee number, which is in both tables.

Thanks in advance.

 

[feherke]

Hi

[blue]db=#[/blue] [b]select[/b] * [b]from[/b] employee;
 id | groupid
----+---------
  1 |       1
  2 |       1
  3 |       2
  4 |       2
  5 |       2
  6 |       3
  7 |       4
(7 rows)

[blue]db=#[/blue] [b]select[/b] * [b]from[/b] deduction;
 employeeid | sum
------------+-----
          1 |  10
          2 |  20
          3 |  30
          4 |  40
          5 |  50
          6 |  60
          7 |  70
(7 rows)

[blue]db=#[/blue] [highlight #eee][b]update[/b] deduction [b]set[/b] sum=sum+1 [b]from[/b] employee e [b]where[/b] e.id=deduction.employeeid [b]and[/b] e.groupid=2;[/highlight]
UPDATE 3

[blue]db=#[/blue] [b]select[/b] * [b]from[/b] deduction;
 employeeid | sum
------------+-----
          1 |  10
          2 |  20
          6 |  60
          7 |  70
          3 |  31
          4 |  41
          5 |  51
(7 rows)

http://postgresql.org/docs/8.1/interactive/sql-update.html

Feherke.

http://rootshell.be/~feherke/