Date: Thu, 14 Sep 2006 09:09:29 +0200
From: Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
To: pgsql-sql@postgresql.org
Subject: Re: How to delete multiple records
Message-ID: <20060914070929.GC60441@spoetnik.xs4all.nl>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <4508F1A7.3040907@sangtao.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4508F1A7.3040907@sangtao.net>
User-Agent: Mutt/1.4.2.1i

On Thu, Sep 14, 2006 at 03:07:35PM +0900, Javanesevn wrote:
> Dear all,
> 
> 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.
> Tell me about other idea? and What's wrong from this query.

The easiest way is to do it like this:

delete from PRODUCT
where research_date < '2006-01-01'

But if you really want to use a subquery, you want to relate it to the 
current record:

delete from PRODUCT
where product_in in (
     select product_id
     from PRODUCT
     where research_date < '2006-01-01'
)

In your example you just test if some records exist. They allways do, so 
the where clause evaluates to true for every row


-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen    reinoud.v@n.leeuwen.net
http://www.xs4all.nl/~reinoud
__________________________________________________