• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4046
  • Last Modified:

DELETE ... WHERE NOT EXISTS problem

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
Renante Entera
Asked:
Renante Entera
1 Solution
 
Z03niECommented:
How about :

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now