Postgres Update Records in Loop

Question

I have an Employee table where DEPTCODE is null.

ID        NAME        AGE        DEPTID      DEPTCODE        
----      ----------  -----      ----------  -------      
1         Paul        32         2                 
2         Allen       25         1

And a Department table like this:

ID        DEPTNAME    DEPTCODE     
----      ----------  -----      
1         HR          DEP-01         
2         ADMIN       DEP-02

How to update DEPTCODE in the Employee table by querying DEPTCODE from the Department table?

I have tried this;

DO $$
BEGIN
FOR depart IN
        SELECT * FROM schema."Department"
    LOOP
        Update table schema."Employee" set "DEPTCODE"=depart."DEPTCODE"
          where "DEPTID"=depart."ID";
    END LOOP;
    END; $$

Are you really still using Postgres 9.1? If yes you should plan your upgrade now. That version is no longer supported — user330315
– user330315, Commented Jul 23, 2018 at 16:37

user330315 · Accepted Answer · 2018-07-23 16:36:43Z

3

No loop necessary. Postgres allows to join two tables in an UPDATE statement:

update "Employee" e
  set "DEPTCODE"=depart."DEPTCODE"
from "Department" depart
where e."DEPTID"=depart."ID";

But you shouldn't really do this. Storing copies of data in related tables it not a good database design. You can always get that information by joining the two tables.

answered Jul 23, 2018 at 16:36

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Update Records in Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related