Solved

How to Delete from a Select query in MySQL?

Posted on 2014-04-29
4
363 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
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 34

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

774 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