Re: Deleting records using the 'LIMIT' clause

[Michael Stassen]

Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,
Ross Honniball <[EMAIL PROTECTED]> writes:
Hi all,
I have positively identified the row I want to delete using:

'SELECT * FROM table LIMIT 10,1'
No, you didn't.  Since you did not include an ORDER BY clause, MySQL
has returned the tenth row according to some completely arbitrary
order criterion.
This has returned 1 record and I now want to DELETE the record.
How do I identify this record in my DELETE statement?
(using 'DELETE FROM table LIMIT 10,1' does not work)
NOTE : I can't identify it using it's key fields as the table has no
primary key.
If you can't identify a record, how are you going to delete it?
Maybe all columns together are something unique; then you can say
  DELETE FROM tbl
  WHERE col1 = val1
    AND col2 = val2
    AND col3 = val3
    ...
Right.  But first you should test it.  Try

  SELECT * FROM tbl
  WHERE col1 = val1
    AND col2 = val2
    AND col3 = val3
to make sure that returns only the row you want to delete.  If it does, then 
 run the delete.

And if no combination of columns uniquely identifies a row (you have 
duplicates), then you must add a primary key column, probably an 
AUTO_INCREMENT.  You probably ought to do that anyway.

Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]