Solved

DELETE ... WHERE NOT EXISTS problem

Posted on 2006-06-14
1
3,936 Views
Last Modified: 2008-01-16
Hi Experts,

I'm currently using MySQL 4.1.11.  I'm just wondering why I couldn't run the query below :
    -------------------------------------
    DELETE FROM `product` AS p
    WHERE NOT EXISTS (
        SELECT * FROM `category`
        WHERE `id` = p.`fk_cat_id`
    )
    -------------------------------------

But using SELECT (it will work fine...):
    -------------------------------------
    SELECT * FROM `product` AS p
    WHERE NOT EXISTS (
        SELECT * FROM `category`
        WHERE `id` = p.`fk_cat_id`
    )
    -------------------------------------

Actually, I have tried running both queries in Sybase without any problem.

So, right now I'm just using another query just to meet the expected result.  And here's the query :
    -------------------------------------
    DELETE FROM `product`
    WHERE `fk_cat_id` NOT IN (
        SELECT `id` FROM `category`
    )
    -------------------------------------

Any idea on how to create a query using EXISTS instead of IN ???  Or, any recommendation as long as performance is concern.

Any help is greatly appreciated...


Thanks!
eNTRANCE2002 :-)
0
Comment
Question by:Renante Entera
1 Comment
 
LVL 5

Accepted Solution

by:
Z03niE earned 500 total points
ID: 16901046
How about :

DELETE `product` FROM `product`
left join `category`  on `product`.`fk_cat_id` = `category`.`id`
WHERE `category`.`id` is null
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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…
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 …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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