[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] How to delete multiple records
From:       Markus Schaber <schabi () logix-tt ! com>
Date:       2006-09-14 7:13:24
Message-ID: 45090114.5020500 () logix-tt ! com
[Download RAW message or body]

Hi, Javanesevn,

Javanesevn wrote:

> I execute this query on below:
> 
>      delete from PRODUCT
>      where exists (
>           select
>                 product_id, item_id
>           from PRODUCT
>           where
>                research_date < '2006-01-01'
>     )
> this query deleted all records data in PRODUCT table. The subquery
> return only some records.

Yes, that's correct.

You don't have any interconnection between the inner and the outer query.

So, for every outer row, there exists an inner row which has
research_date < '2006-01-01'.

> Tell me about other idea? and What's wrong from this query.

For this simple case, why don't you do:

DELETE FROM PRODUCT WHERE research_date < '2006-01-01'

Or do you want to delete all entries for products that have at least one
entry with a matching research date?

Then go along something like

DELETE FROM product WHERE EXISTS (
    SELECT * from product innr
        WHERE research_date < '2006-01-01'
        AND product_id = innr.product_id
        AND item_id = innr.item_id
);


HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly
[prev in list] [next in list] [prev in thread] [next in thread]