SQL reinsert row in the same table

Seems that we have a competitor to “export to csv” sql query that I keep forgetting every time I need to export something in the CSV file.

The second nominee is reinserting the row into the same database but with a new primary key.

# YOURID - source record
# yourtable - target table;

CREATE TEMPORARY TABLE tmp_table SELECT * FROM yourtable WHERE id=YOURID; 
SELECT @new_id:=((SELECT id FROM yourtable ORDER BY id DESC LIMIT 1) + 1); 
UPDATE tmp_table SET id=@new_id WHERE id =YOURID; 
INSERT INTO yourtable SELECT * FROM tmp_table WHERE id=@new_id;

 

Leave a Reply