From: Markus Schaber <schabi@logix-tt.com>
To: Javanesevn <thanhle@sangtao.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to delete multiple records
Date: Thu, 14 Sep 2006 09:13:24 +0200
Message-ID: <45090114.5020500@logix-tt.com> (raw)
In-Reply-To: <4508F1A7.3040907@sangtao.net>
References: <4508F1A7.3040907@sangtao.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: schabi@logix-tt.com, thanhle@sangtao.net
Subject: Re: How to delete multiple records
In-Reply-To: <45090114.5020500@logix-tt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox