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
Solved

How to Delete from a Select query in MySQL?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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