Copy all rows changing only one field in MySQL

Question

I have a table with the following fields

ID, date, attribute

I want to duplicate all of the rows but with the duplicates I want the ID to be different. The current ID is 241, I want the ID of the copied rows to be 251.

INSERT INTO table (ID, date, attribute) SELECT (251, date, attribute) FROM attendance WHERE ID = 241

as per the post here - MySQL: How to copy rows, but change a few fields? but I can't get it to work. What am I doing wrong?

"I can't get it to work" is not a problem description. What happens when you run it? Why is that wrong? — underscore_d, Nov 30 '20 at 16:38
You shouldn't have parentheses around the list of columns after `SELECT`. — Barmar, Nov 30 '20 at 16:41
You are correct Barmer. Sorry underscore_d I should have been more specific — JeremyRenner2, Nov 30 '20 at 16:48

Adnan Ahmed · Answer 1 · 2020-11-30T16:50:24.403

0

CREATE table temporary_table AS SELECT * FROM original_table WHERE <conditions>;

UPDATE temporary_table SET <fieldx>=<valuex>, <fieldy>=<valuey>, ...;

UPDATE temporary_table SET <auto_inc_field>=NULL;

INSERT INTO original_table SELECT * FROM temporary_table;

DROP TABLE temporary_table

You can try this. Its an alternative solution in the answer given to

MySQL: How to copy rows, but change a few fields?

edited Nov 30 '20 at 16:50

answered Nov 30 '20 at 16:43

Adnan Ahmed

466
1
6
15

1

What did you change? Why does that fix the problem? Don't just drop code without context. – underscore_d Nov 30 '20 at 16:47

score 0 · Answer 2 · answered Nov 30 '20 at 16:43

You have to remove the parenthesis from the SELECT

CREATE TABLE attendance (ID int,`date` date, attribute varchar(5))

✓

CREATE TABLE table1 (ID int,`date` date, attribute varchar(5))

✓

INSERT INTO attendance VALUES (241,NOW(),'YES1'),(241,NOW(),'YES2'),(241,NOW(),'YES3')

✓

INSERT INTO table1 (ID, `date`, attribute) SELECT 251, `date`, attribute FROM attendance WHERE ID = 241

✓

SELECT * FROM table1

 ID | date       | attribute
--: | :--------- | :--------
251 | 2020-11-30 | YES1     
251 | 2020-11-30 | YES2     
251 | 2020-11-30 | YES3

db<>fiddle here

Copy all rows changing only one field in MySQL

2 Answers2