时间戳

首页 > 解决方案 > 如何更新列的值？

sql - 如何更新列的值？

问题描述

我在 PostgreSQL 数据库中PROJECTS有2 个表。PROJECTS_EMPLOYEES_RELATIONSHIP如您所见，在第一个表中，我有一列名为TOTAL_EMPLOYEES. 在该列中，我想根据第二张表的信息存储项目中的员工总数。我尝试了下一条 sql 语句，但它引发了错误。如何修复此错误？

项目：

| 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  | NNogerbek@gmail.com   |
| 41c89a24-fff2-4cbc-a542-b1e956a352f9  | IKim@gmail.com        |

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;

错误：

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

标签： sqlpostgresql

解决方案

您可以在下面尝试 - 请参阅更新加入作为参考

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

推荐阅读