CODEWITHSUNDEEP
mysqlsubqueryinner-join
Bas
Bas

Reputation: 2210

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 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?

Upvotes: 1

Views: 51

Answers (3)

Anoop Butola
Anoop Butola

Reputation: 50

@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

Upvotes: 0

Anoop Butola
Anoop Butola

Reputation: 50

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/

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Related Questions