How to delete rows in MySQL, but force the numbering of all next rows to the number of my last id?

I have a table with 1000 rows.开发者_运维百科

I need to delete rows from 5 to 1000, so the first 4 remain intact and eveythin after 4 is deleted.

However if I write something like:

DELETE FROM table_name WHERE id > 4

the numbering of the next record after INSERT is 1001, which I do not want.

How to delete rows, but force the numbering of all next rows to the number of my last id?

P.S. I can not use ALTER TABLE to drop all the table.

Thanks in advance.

Can you do this?


ALTER TABLE theTableInQuestion AUTO_INCREMENT=5

If your id - auto_increment field you need to use ALTER TABLE to change it's value...

But if it's impossible you can try not to delete rows but to set there value to NULL or "" or 0 and then just to update there value not INSERT but UPDATE ... WHERE id = 5

With MySQL? You don't. AUTO_INCREMENT never... decrements unless you call ALTER TABLE. Have you considered use of a trigger DELETE, however?

(You may need to debug this)

delimiter |

CREATE TRIGGER testref AFTER DELETE ON theTableInQuestion
  FOR EACH ROW BEGIN
    IF SELECT COUNT(*) FROM test1 < 5 THEN
       ALTER TABLE theTableInQuestion AUTO_INCREMENT=5
    END IF;
  END;
|

DBCC CHECKIDENT ( 'table_name', RESEED, new_reseed_value ) ==> Works with MS-SQL

whereas

ALTER TABLE table_name AUTO_INCREMENT = 5 ==> Works with MySQL

Think the table is in auto_increment . If you cant ALTER TABLE you need to manualy give the id in each query, something like

 INSERT INTO table (id,value) VALUES ((SELECT MAX(id)+1 FROM table), value);

(see comment vis-a-vis lock table)