Bas Bas - 1 month ago 7
MySQL Question

MySQL UPDATE with needed subquery

For school I've got the following assignment to UPDATE data into a database:


Give all the employees (located into the employee table)
who work on a project where the employee "50" (employee code) works on aswell
a salary raise of 250 euro


I know I had to get the
SAL
columm in the employee and then raise it by 250 euro. I currently did this by the following query.

UPDATE `work`
AS work

INNER JOIN `employee` AS employee
on employee.`CODE` = work.`W_CODE`

SET employee.`SAL` = (employee.`SAL` + 250)

WHERE work.`P_CODE` IN
(
# ....
);


The
P_CODE
column stands for the project identifier. In the
work
table, all employee's with the current project they are working on are listed.

For getting the projects where the employee with the code 50 worked on, I made the following subquery:

UPDATE `work`
AS work

INNER JOIN `employee` AS employee
on employee.`CODE` = work.`W_CODE`

SET employee.`SAL` = (employee.`SAL` + 250)

WHERE work.`P_CODE` IN
(
SELECT work.`P_CODE`
FROM `work` AS work

INNER JOIN `employee` AS employee
ON `employee`.`CODE` = work.`W_CODE`

WHERE employee.`CODE` = "50"
);


When running this query, it gives back the following error:


Error Code: 1093. Table 'work' is specified twice, both as a target
for 'UPDATE' and as a separate source for data


For my own try, I did some research and found I can't use the same table twice for this.

Questions

How can I fix this query where the sub-query will work? Can it be combined in my first join?

Answer

try using different alias eg: w1 and w2 for work

UPDATE `work` 
    AS w1

    INNER JOIN `employee` AS employee
        on employee.`CODE` = w1.`W_CODE`

    SET employee.`SAL` = (employee.`SAL` + 250)

    WHERE w1.`P_CODE` IN  ( select t.my_code from (
    SELECT w2.`P_CODE` as my_code 
        FROM `work` AS w2
        INNER JOIN `employee` AS employee
            ON `employee`.`CODE` = w2.`W_CODE`
        WHERE employee.`CODE` = "50"
    )  t ) ;
Comments