Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 26 '13 at 13:14

This question came from our site for professional and enthusiast programmers.

3  
I personally couldn't understand your problem. –  Dead Man Mar 26 '13 at 12:43
2  
There is no 'first' records in sql. Only if you order it by some field you can delete with limit. We can not guess what is written in your book so can not help you understand whether you're right. –  Aneri Mar 26 '13 at 12:44
1  
What do you mean by "stable"? –  booyaa Mar 26 '13 at 13:18

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.