There are two tables users and del , it is required to move one entry from the users table to the del table, users have an auto-increment. You need a transfer request from users=>del and del=>users , and at the same time save the id (auto increment).

  • 2
    To put the column del not an option? update it instead of fancy transfers? - Yura Ivanov
  • in my opinion, the @YuraIvanov case says - to move records in this case - just waste resources and as the old Occam said : “Do not multiply things without need” - MaxU
  • If to make in all requests in addition WHERE on a column del. Performance drops by 1% 2% or more, because an additional comparison is required (after all, 99% of users del will be false (0)) further, the muscle will always stumble over this record. Transferring it to another table, I get rid of additional checks (and checks with a del column of at least 20 queries, the project is large) and the muscle will not stumble. Further, for example, you set additionally Where del = 0, and you did not think that the muscle will find the record del = 1 and will it be further to unscrew the entire table? - Denis Kotlyarov
  • @DenisKotlyarov hmm, a big project ... Do you have a foreign key anywhere on users? Or do you have a cascade delete? Why do you then save only users? Holivar theme "soft delete" vs "hard delete", but it seems your arguments do not take into account some of the nuances ... - Yura Ivanov

2 answers 2

You can do the following:

 START TRANSACTION; INSERT INTO del SELECT * FROM users WHERE id = 354; DELETE FROM users WHERE id = 354 LIMIT 1; COMMIT;

In the transaction, we execute two queries - the first to copy the record with the selected id from the users table to the del table. The second is deleting the record from the table.

  • Ok, I will try, there is not enough Limit :) - Denis Kotlyarov
  • It seems to me here it is redundant, but I agree to start a query with LIMIT - a good habit, especially if you often work in the console :) - cheops
  • Schyas for now, sorry, I can not protest, it will be a little different. Soon I’m going to work and if it works (and I think it will be so), I’ll put it +. - Denis Kotlyarov

Save the auto increment will be difficult. For example, mysqldump does not save the state of autoincrement. After the restoration of the database, the auto increment will take the nearest number. As an option, use the users_numerator table-enumerator with auto-increment, and associate with it users and del by id