pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to delete multiple records
3+ messages / 3 participants
[nested] [flat]
* How to delete multiple records
@ 2006-09-14 06:07 Javanesevn <thanhle@sangtao.net>
0 siblings, 2 replies; 3+ messages in thread
From: Javanesevn @ 2006-09-14 06:07 UTC (permalink / raw)
To: pgsql-sql
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.
Best regards,
-Javanesevn
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to delete multiple records
@ 2006-09-14 07:09 Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
parent: Javanesevn <thanhle@sangtao.net>
1 sibling, 0 replies; 3+ messages in thread
From: Reinoud van Leeuwen @ 2006-09-14 07:09 UTC (permalink / raw)
To: pgsql-sql
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
__________________________________________________
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to delete multiple records
@ 2006-09-14 07:13 Markus Schaber <schabi@logix-tt.com>
parent: Javanesevn <thanhle@sangtao.net>
1 sibling, 0 replies; 3+ messages in thread
From: Markus Schaber @ 2006-09-14 07:13 UTC (permalink / raw)
To: Javanesevn <thanhle@sangtao.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-09-14 07:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-14 06:07 How to delete multiple records Javanesevn <thanhle@sangtao.net>
2006-09-14 07:09 ` Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
2006-09-14 07:13 ` Markus Schaber <schabi@logix-tt.com>
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