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);
You need parenthesis around your subrequest.
update employee e
set projectId = concat('project:',
(select projectName
from projects
where projectId = substring_index(e.projectId, ':', -1)));
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments