Tags: MySQL

You can't specify target table 'user_master' for update in FROM clause error comes up when you try to perform an update or delete operation while selecting data from table it self. This is something like How to update/delete self table in MySQL? We will produce same error and then will give you solution for the same.

Problem

/* UPDATING RECORDS */
UPDATE user_master
SET first_name = 'javaQuery'
WHERE id IN (SELECT id FROM user_master WHERE last_name = 'Jobs');

/* DELETING RECORDS */
DELETE 
FROM user_master
WHERE id IN (SELECT id FROM user_master WHERE user_name = 'javaQuery');

We are fetching all users whose Lastname is Jobs and replacing its Firstname in 1st query and deleting records in second query. This won't allow you to update or delete records.


Solution

/* UPDATING RECORDS */
UPDATE user_master
SET first_name = 'javaQuery'
WHERE id IN (SELECT * FROM (SELECT id FROM user_master WHERE user_name = 'javaQuery') AS userIDs);

/* DELETING RECORDS */
DELETE
FROM user_master
WHERE id IN (SELECT * FROM (SELECT id FROM user_master WHERE user_name = 'javaQuery') AS userIDs);

What we did?

• We are selecting data from query SELECT id FROM user_master WHERE user_name = 'javaQuery' to temporary table called userIDs.
• Select data from temporary table and use it in WHERE condition.

Tags: MySQL