Duplicate record in database with new id

Question

Navigation

#1 by (2 votes)

1

I have a table in the database called user, with n fields, how can I duplicate a certain record only with a new id?

insert into usuarios * select * from usuarios where id = x?

obs: this id is a non-null primary key

mysql sql mariadb

asked by anonymous 18.07.2017 / 03:02

1 answer

Check if a key exists in a dictionary Create random classes and ids

score 2 · Accepted Answer

You can make a INSERT SELECT at the same time, like this:

INSERT INTO tabela (campo1, caompo2, ...) 
   SELECT campo1, caompo2, ... FROM tabela WHERE primary_key = 3;

Note:

You must pass the same amount of fields in insert and select .
It is not mandatory to pass all the fields of the table, but the do not pass fields are null .
You do not need to pass the primary key to make a copy of the registry.

If you want to copy the line without specifying all the fields, you can do this:

Create a temporary table and associate the line you want to copy

CREATE TEMPORARY TABLE tmp SELECT * FROM tabela WHERE id = 3;

Update the temp table with a new id

UPDATE tmp SET id_conta = 4 WHERE id_conta = 3;

Now, just apply INSERT

INSERT INTO conta SELECT * FROM tmp WHERE id_conta = 4;