Solved

How to Delete from a Select query in MySQL?

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now