Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Delete from a Select query in MySQL?

Posted on 2014-04-29
4
Medium Priority
?
382 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 1000 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 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 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