Mysql – how to use thesql to delete only the first ten table records

MySQLPHP

I'm currently using php and mysql together. What I want to try is if I have ten "stable" records for the database and in the same .php file I have some text boxes so I can type in then what I typed will be saved into database but each time I goes to the page only the ten records will be deleted and recreated and the ones I entered will NOT be deleted. Is it possible? I know sounds weird but I'm reading the book and there's such task. Well the task didn't mention clearly if all should be deleted or just the ten "stable" records.

Am I not making much of a sense here?

There are like quite lots scripts for what I have already done so I didn't paste the codes here and wonder if I can get a concept first.

Best Answer

I can understand you saying "stable". There is no clean mechanism in MySQL to implicitly govern the order in which MySQL can delete rows using LIMIT. It is actually not transaction-safe for MySQL Replication.

The only way to guarantee an orderly deletion of rows is to delete by a PRIMARY KEY or UNIQUE KEY that does not rely on an auto_increment column.

EXAMPLE OF STABLE DELETE

Suppose you want to delete the first 10 employees in a company table (layoffs do hurt, this is only an example)

CREATE TABLE emp
(
    id INT NOT NULL AUTO_INCREMENT,
    empid INT NOT NULL,
    ...
    PRIMARY KEY (id),
    UNIQUE KEY (empid)
);

You can safely delete the 10 employees as follows:

CREATE TABLE empids_to_delete
SELECT empid FROM emp ORDER BY empid LIMIT 10;
ALTER TABLE empids_to_delete ADD PRIMARY KEY (empid);
DELETE B.* FROM empids_to_delete A INNER JOIN emp B USING (empid);
DROP TABLE empids_to_delete;

This would be great as long as you create the empid outside this table. This would not present a Replication Slave with a problem even if the auto_increment sequence on a Master is completely different from that of the Slave.

Related Question