CLONE RECORDS ON THE SAME TABLE AND THE AUTO_INCREMENT NEED TO UPDATE AT THE SAME TIME

Assuming the AUTO ID field is the first one in the table (a usual case), we can make use of implicit transactions.

CREATE TEMPORARY TABLE tmp SELECT * from invoices WHERE …;
ALTER TABLE tmp drop ID; # drop autoincrement field
# UPDATE tmp SET …; # just needed to change other unique keys
INSERT INTO invoices SELECT 0,tmp.* FROM tmp;
DROP TABLE tmp;
From the MySQL docs:

Using AUTO_INCREMENT: You can also explicitly assign NULL or 0 to the column to generate sequence numbers.
shareimprove this answer
edited Nov 10 ’11 at 10:33

Jonathan Spooner
5,89922135
answered Nov 10 ’11 at 10:18

Tim Ruehsen
53142
2
This was really useful for me, thanks. – rotanimod Jan 20 ’12 at 20:07

Exactly what I wanted, thanks! – Yos Feb 24 ’14 at 16:15

This put the record in with an ID of 0 for me, even though I have an AUTO_INCREMENT field. Changing it to null worked, so I suggest you change the code to use null instead of 0. I’m on MySQL 5.5.35 (Ubuntu). – Tyler Collier Mar 21 ’14 at 21:24

@TylerCollier: NULL or 0 both are acceptable. Can you post screen shots of the issue by reproducing and share it on here. – Ravinder Reddy Apr 11 ’14 at 5:25

My brain hurt trying to understand the neato subquery in line 4. Maybe this explanation helps: The destination table invoices contains an AUTO_INCREMENT ID field. The source table tmp does not. In order to get the needed 1:1 correlation during the insert, you specify a 0 as the first field of the select which is the destination table’s AUTO_INCREMENT field. The tmp.* selects all the fields from the source table tmp. Perfect! You now have your 1:1 correlation. Awesome answer @Tim. – elbowlobstercowstand May 14 ’15 at 8:15

@RavinderReddy NULL and 0 are not the same – it is possible to have a record-id of 0 – use NULL for this sort of thing – Tobias Beuving Aug 31 ’15 at 12:16

@TobiasBeuving: I am well aware that NULL and 0 are not the same. Current context is on AUTO_INCREMENT feature. You can’t insert a 0 or a NULL value into such fields. But when you use them as inputs into such field, next sequence value will be assigned to that field automatically. And hence my comment. Proof on SQL Fiddle: sqlfiddle.com/#!9/d619f/1 – Ravinder Reddy Sep 1 ’15 at 8:48

BIG WARNING!! The ALTER TABLE causes an implicit transaction commit, even though it’s on a temporary table. And, actually, you should do DROP TEMPORARY TABLE tmp; to avoid the same on the final line, though it’s too late by then. – Lightness Races in Orbit 2 days ago

SOURCE: http://stackoverflow.com/questions/729489/duplicate-copy-records-in-the-same-mysql-table

FROM THE AUTHOR I TRIED  THIS WAY!


mysql> create database payroll;
mysql> use payroll;
mysql> create table pay (id bigint(15) auto_increment primary key, fname varchar(10) comment 'first name');
mysql> desc pay;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(15) | NO | PRI | NULL | auto_increment |
| fname | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.13 sec)

INSERT THE RECORDS;

mysql> insert pay (fname) values ("a");
mysql> insert pay (fname) values ("b");
mysql> insert pay (fname) values ("c");
mysql> select * from pay;
+----+-------+
| id | fname |
+----+-------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+-------+
9 rows in set (0.00 sec)

NOW LET US CLONE THE TABLE BY CREATING A TEMPORARY TABLE IN MEMORY.
mysql>create temporary table tmp engine=memory select * from pay;

LET US SEE OUR tmp TABLE;

mysql> select * from tmp;
+----+-------+
| id | fname |
+----+-------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+-------+
3 rows in set (0.00 sec)

NOW LET US TRANSFER ALL THE RECORDS OF TMP TABLE TO OUR PAY TABLE WITHOUT DUPLICATING THE ID.

THE USE OF “NULL”;

mysql> insert into pay select null, fname from tmp;
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0

SEE THE RECORDS;

mysql> select * from pay;
+----+-------+
| id | fname |
+----+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | a |
| 5 | b |
| 6 | c |
+----+-------+
6 rows in set (0.00 sec)

THE USE OF ZERO (0); I HAVE ONLY ONE FIELD FNAME

mysql> insert into pay select 0, fname from tmp;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from pay;
+----+-------+
| id | fname |
+----+-------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | a |
| 5 | b |
| 6 | c |
| 7 | a |
| 8 | b |
| 9 | c |
+----+-------+
9 rows in set (0.00 sec)

DELETE THE TEMPORARY TABLE;

mysql> drop table tmp;
Query OK, 0 rows affected (0.05 sec)

ADDING ANOTHER COLUMN LNAME;

mysql> alter table pay add column lname varchar(10) comment 'lastname';
Query OK, 9 rows affected (1.08 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> desc pay;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(15) | NO | PRI | NULL | auto_increment |
| fname | varchar(10) | YES | | NULL | |
| lname | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.09 sec)
mysql> select * from pay;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 1 | a | NULL |
| 2 | b | NULL |
| 3 | c | NULL |
| 4 | a | NULL |
| 5 | b | NULL |
| 6 | c | NULL |
| 7 | a | NULL |
| 8 | b | NULL |
| 9 | c | NULL |
+----+-------+-------+
9 rows in set (0.00 sec)

FILL-UP THE lname as x,y,z

mysql> select * from pay;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 1 | a | x |
| 2 | b | y |
| 3 | c | z |
| 4 | a | x |
| 5 | b | y |
| 6 | c | z |
| 7 | a | x |
| 8 | b | y |
| 9 | c | z |
+----+-------+-------+
9 rows in set (0.00 sec)
mysql> create temporary table tmp engine=memory select * from pay;
Query OK, 9 rows affected (0.13 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> select * from tmp;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 1 | a | x |
| 2 | b | y |
| 3 | c | z |
| 4 | a | x |
| 5 | b | y |
| 6 | c | z |
| 7 | a | x |
| 8 | b | y |
| 9 | c | z |
+----+-------+-------+
9 rows in set (0.00 sec)
mysql> select * from pay;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 1 | a | x |
| 2 | b | y |
| 3 | c | z |
| 4 | a | x |
| 5 | b | y |
| 6 | c | z |
| 7 | a | x |
| 8 | b | y |
| 9 | c | z |
+----+-------+-------+
9 rows in set (0.00 sec)

TRY TO CLONE!! using 0, tmp.*;
OR
TRY TO CLONE!! using null, tmp.*;

to be continued.........

Advertisements
This entry was posted in MYSQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s