0

This question already has an answer here:

I have two tables TableA, TableB as follows.

Table "db3004db.tableb"

 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 empid   | integer |           | not null | 
 salaryb | integer |           | not null | 

Indexes:
    "tableb_pk" PRIMARY KEY, btree (empid)

Table "db3004db.tablea"

 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 empid  | integer |           | not null | 
 salary | integer |           | not null | 

Indexes:
    "tablea_pk" PRIMARY KEY, btree (empid)

Before Update:

select * from tableA;

 empid | salary 
-------+--------
     1 |   1004
     2 |   2007

select * from tableB;

 empid | salaryb 
-------+---------
     1 |       0
     2 |       0

Expected Update Query:

set tableB.salary=tableA.salary%1000, 
    tableA.salary= tableA.salary/1000 
where (tableA.pk = tableB.pk)

After update:

select * from tableA;

 empid | salary 
-------+--------
     1 |   1
     2 |   2

select * from tableB;

 empid | salaryb 
-------+---------
     1 |       4
     2 |       7

I'm interested in doing this update in Postgres (with single update statement)? If possible , is this query compatible with SQL standard?

marked as duplicate by a_horse_with_no_name postgresql Aug 24 '18 at 10:37

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Standard SQL does not even allow a join in an UPDATE statement, let alone updating more than one table in it. – a_horse_with_no_name Aug 24 '18 at 10:37
0
WITH cte 
AS ( UPDATE tableB 
     SET salaryb = tableA.salary % 1000 
     FROM tableA 
     WHERE tableB.empid = tableA.empid
     RETURNING *
   )
UPDATE tableA
SET salary = salary / 1000;

Not the answer you're looking for? Browse other questions tagged or ask your own question.