In MySQL, can I copy one row to insert into the same table?

by

I used Leonard Challis’s technique with a few changes:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

As a temp table, there should never be more than one record, so you don’t have to worry about the primary key. Setting it to null allows MySQL to choose the value itself, so there’s no risk of creating a duplicate.

If you want to be super-sure you’re only getting one row to insert, you could add LIMIT 1 to the end of the INSERT INTO line.

Note that I also appended the primary key value (1 in this case) to my temporary table name.

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  3. Find duplicate records in MySQL
  4. How to delete duplicates on a MySQL table?
  5. How do I add indexes to MySQL tables?
  6. How to abort INSERT operation in MySql trigger?
  7. How to copy data from one table to another new table in MySQL?
  8. Prevent auto increment on MySQL duplicate insert
  9. Deleting duplicate rows from a table
  10. How to copy a row and insert in same table with a autoincrement field in MySQL?
  11. MySQL: Cloning a MySQL database on the same MySql instance
  12. Removing duplicates with unique index
  13. How to delete duplicate rows from a MySQL table
  14. How do I delete all the duplicate records in a MySQL table without temp tables
  15. MySQL delete duplicate records but keep latest
  16. How to remove duplicate entries from a mysql db?
  17. Count duplicates records in Mysql table?
  18. How to do unique constraint works with NULL value in MySQL
  19. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
  20. MySQL how to fill missing dates in range?
  21. How to split the name string in mysql?
  22. How to set a maximum execution time for a mysql query?
  23. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  24. Schrödingers MySQL table: exists, yet it does not
  25. ld: library not found for -lzstd while bundle install for mysql2 gem Ruby on macOS Big Sur 11.4 Apple M1
  26. How to migrate SQL Server database to MySQL? [closed]
  27. how to cast the hexadecimal to varchar(datetime)?
  28. Rank() over Partition by in mysql
  29. delete rows from multiple tables
  30. MySQL Multiple Joins in one query?

Leave a Comment