Solved

How to Delete from a Select query in MySQL?

Posted on 2014-04-29
4
370 Views
Last Modified: 2014-04-30
Hi,

I am trying to delete from a SELECT query, I have two tables that I want to check against eachother, and delete entries from one that does not exist in the other:

-- This is the error I get (query syntax is below, note the select query works fine):
#1093 - You can't specify target table 'health4_mt_address_lock' for update in FROM clause

DELETE FROM health4_mt_address_lock WHERE address_id IN (SELECT l.address_id FROM health4_mt_address_lock AS l LEFT JOIN health4_mt_address AS a on l.address_id = a.address_id
WHERE a.address_id IS NULL);

Open in new window

0
Comment
Question by:jwleys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 8

Assisted Solution

by:Ganapathi
Ganapathi earned 250 total points
ID: 40031210
DELETE health4_mt_address_lock where address_id NOT IN (SELECT address_id FROM health4_mt_address)

Open in new window

0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 250 total points
ID: 40031221
When using MySQL to UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query.

You can use a workaround:
DELETE FROM health4_mt_address_lock WHERE address_id IN (
SELECT l.address_id FROM (
SELECT address_id FROM health4_mt_address_lock
) AS l LEFT JOIN health4_mt_address AS a on l.address_id = a.address_id
WHERE a.address_id IS NULL);

Open in new window

HTH,
Dan
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40031380
Hi,

Please try the following Query


DELETE FROM health4_mt_address_lock
WHERE address_id
NOT IN (SELECT DISTINCT address_id FROM health4_mt_address WHERE address_id IS NOT NULL)
0
 

Author Closing Comment

by:jwleys
ID: 40033409
I ended up using the solution from Ganapathi S although it did have missing syntax I used:
DELETE FROM health4_mt_address_lock where address_id NOT IN (SELECT a.address_id FROM health4_mt_address AS a);

I wanted to also award points to Dan Craciun (your solution also worked) as you taught me :
"When using MySQL to UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query."
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question