469,106 Members | 2,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

Deleting multiple rows that have the same field value.

Hello,

Example:

I have 100 rows that all have the field vaule of 'XYZ' I would like to
delete only 10 of those rows.

Is there such a query like "Delete 10 rows from table where table.field
= 'XYZ'"

Thanks,

Patrick

Nov 2 '05 #1
7 5279
>I have 100 rows that all have the field vaule of 'XYZ' I would like to
delete only 10 of those rows.

Is there such a query like "Delete 10 rows from table where table.field
= 'XYZ'"


Have you tried:
delete from table where table.field = 'XYZ' limit 10;
?

This is one of the reasons why a field:
id int not null auto_increment primary key
is useful. It guarantees you will be able to generate a query to just
delete what you want to delete.

Gordon L. Burditt
Nov 2 '05 #2
Hey thanks... that worked.

My table does have auto_inc, int primary key... How can I do my quiery
if the rows are not in consecutive order?

delete from table where table.id = '1' and table.id ='2' and table.id =
'10' etc....

Patrick

Nov 2 '05 #3
pa**************@gmail.com wrote:
Hey thanks... that worked.

My table does have auto_inc, int primary key... How can I do my quiery
if the rows are not in consecutive order?

delete from table where table.id = '1' and table.id ='2' and table.id =
'10' etc....


delete from table where id in (select id from table where field='XYZ'
limit 10)

First time posting and may not be right, but that'd be where I'd start from.

Cheers,
Andy
Nov 2 '05 #4
> delete from table where id in (select id from table where field='XYZ'
limit 10)


In theory that's right, but I think there's still a limitation in MySQL that
doesn't allow to use sub-selects in delete statements that refer to the same
table as the outer query. So I fear that this query will not work, yet.

Markus
Nov 2 '05 #5
>My table does have auto_inc, int primary key... How can I do my quiery
if the rows are not in consecutive order?

delete from table where table.id = '1' and table.id ='2' and table.id =
'10' etc....


There's also:

delete from table where table.id in (1, 2, 10, 86, 37, 666);

Gordon L. Burditt
Nov 2 '05 #6
Markus Popp wrote:
delete from table where id in (select id from table where field='XYZ'
limit 10)


In theory that's right, but I think there's still a limitation in MySQL that
doesn't allow to use sub-selects in delete statements that refer to the same
table as the outer query. So I fear that this query will not work, yet.


OK, well nice to know it should work in theory (do you know if it would
work in MySQL 5.0?).

More out of interest than anything else....
Cheers,
Andy
Nov 2 '05 #7
> OK, well nice to know it should work in theory (do you know if it would
work in MySQL 5.0?).


Unfortunately not.

It might be possible to do it with a join - but it might be easier to choose
the

delete from table where id in (1, 4, 6, 8);

way.

Markus
Nov 2 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Gary | last post: by
3 posts views Thread by Epetruk | last post: by
12 posts views Thread by shank | last post: by
4 posts views Thread by Jim Michaels | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.