Deleting multiple rows that have the same field value.

[patrick.mcdonald]

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

[Gordon Burditt]

>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

[patrick.mcdonald]

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

[Andy Jeffries]

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

[Markus Popp]

> 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

[Gordon Burditt]

>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

[Andy Jeffries]

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

[Markus Popp]

> 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