From: Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
To: pgsql-sql@postgresql.org
Subject: Re: How to delete multiple records
Date: Thu, 14 Sep 2006 09:09:29 +0200
Message-ID: <20060914070929.GC60441@spoetnik.xs4all.nl> (raw)
In-Reply-To: <4508F1A7.3040907@sangtao.net>
References: <4508F1A7.3040907@sangtao.net>
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
__________________________________________________
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: reinoud.v@n.leeuwen.net
Subject: Re: How to delete multiple records
In-Reply-To: <20060914070929.GC60441@spoetnik.xs4all.nl>
* 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