CODEWITHSUNDEEP
sqlpostgresql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5236

How to update value to the column?

I have 2 table called PROJECTS and PROJECTS_EMPLOYEES_RELATIONSHIP in PostgreSQL database. In first table as you can see I have column called TOTAL_EMPLOYEES. In that column I want to store total number of employees in the project based on the information of 2nd table. I tried next sql statement but it raise error. How to fix this error?

PROJECTS:

| ID (uuid)                             | TOTAL_EMPLOYEES (int) |
|---------------------------------------|-----------------------|
| 36c89a24-fff2-4cbc-a542-b1e956a352f9  | 0                     |
| 41c89a24-fff2-4cbc-a542-b1e956a352f9  | 5                     |

PROJECTS_EMPLOYEES_RELATIONSHIP:

| PROJECT_ID (uuid)                     | EMPLOYEE (varchar)    |
|---------------------------------------|-----------------------|
| 36c89a24-fff2-4cbc-a542-b1e956a352f9  | [email protected]   |
| 41c89a24-fff2-4cbc-a542-b1e956a352f9  | [email protected]        |

SQL:

UPDATE PROJECTS
SET TOTAL_EMPLOYEES = COUNT(EMPLOYEE)
FROM PROJECTS
LEFT JOIN PROJECTS_EMPLOYEES_RELATIONSHIP
ON PROJECTS.ID = PROJECTS_EMPLOYEES_RELATIONSHIP.PROJECT_ID
WHERE PROJECTS.ID = PROJECTS_EMPLOYEES_RELATIONSHIP.PROJECT_ID;

ERROR:

SQL Error [42712]: ERROR: table name "PROJECTS" specified more than once.

Upvotes: 0

Views: 48

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below - see update join as reference

UPDATE PROJECTS
SET TOTAL_EMPLOYEES = cntemployee 
FROM (SELECT PROJECT_ID, COUNT(EMPLOYEE) AS cntemployee 
      FROM PROJECTS_EMPLOYEES_RELATIONSHIP
      GROUP BY PROJECT_ID) B
WHERE PROJECTS.ID = B.PROJECT_ID

Upvotes: 1

Related Questions