rockyit86 rockyit86 - 6 months ago 8
SQL Question

How to update mysql column with another value from another table?

How to replace the value in 'projectId' column with project name assuming there is another table with name 'project' and two tables are related on the number mentioned after ':' in 'projectId' column of employee output.

> select * from employee;
+----+-----------+
| id | projectId |
+----+-----------+
| 1 | project:1 |
+----+-----------+


Desired output :

+----+-----------------------+
| id | projectId |
+----+-----------------------+
| 1 | project:internProject |
+----+-----------------------+


Steps required:


extract the integer [done]


update employee SET projectId = substring_index(projectId,':', -1);



update the projectID with name [not sure :( ]


update employee SET projectId = concat('project:', select projectName from projects where projects.projectID = employee.projectId);

Answer

You need parenthesis around your subrequest.

update employee e
set projectId = concat('project:',
                       (select projectName
                        from projects
                        where projectId = substring_index(e.projectId, ':', -1)));
Comments