MySQL UPDATE with needed subquery

Question

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 as well a salary raise of 250 euro

I know I had to get the SAL column 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, I get this 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?

ScaisEdge · Accepted Answer · 2016-10-08 18:36:36Z

1

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 ) ;

edited Oct 8, 2016 at 18:36

answered Oct 8, 2016 at 18:18

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Bas Over a year ago

It gives Error Code: 1093. Table 'w1' is specified twice, both as a target for 'UPDATE' and as a separate source for data back.. I don't even understand what's going on.

ScaisEdge Over a year ago

i have update the answer forcing a dinamic temp table

Bas Over a year ago

Why is the t there at the end?

ScaisEdge Over a year ago

@bas t is a dinamic temp table name . when you create a table this way you must assign a name .. for eventually refer to the table column .. in you case you don't need to refer to this table column but in some case you need eg: work.p_code = t.my_code ..

Bas Over a year ago

But aren't you doing that with the my_code alias?

|

Anoop Butola · Accepted Answer · 2016-10-08 19:55:20Z

0

update employee as e1 set e1.Salary=(e1.Salary+20) where e1.workId IN ( select workId from ( select e2.workId from employee as e2 inner join works as w2 on e2.workId=w2.id where e2.workId=50 ) as x)

Refer link for detail explanation: http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

answered Oct 8, 2016 at 19:55

Anoop Butola

501 gold badge1 silver badge5 bronze badges

Comments

Anoop Butola · Accepted Answer · 2016-10-08 20:06:37Z

0

@Bas in above query i have considered employee table to be updated so u can make changes accordingly.

this query works fine if structure is like this:

1)Employee table .............. empid(P key) | workId(F key) | Name |salary

2)Work table id (P key) | workcode

answered Oct 8, 2016 at 20:06

Anoop Butola

501 gold badge1 silver badge5 bronze badges

Collectives™ on Stack Overflow

MySQL UPDATE with needed subquery

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related