I am proposing a new design that will normalize the customers in each batch
DROP DATABASE IF EXISTS ganeshbora;
CREATE DATABASE ganeshbora;
USE ganeshbora
CREATE TABLE customer
(
cust_id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(20),
lastname VARCHAR(20),
address VARCHAR(40),
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
CREATE TABLE batch_sequence
(
batch_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (batch_id)
) ENGINE=MyISAM;
CREATE TABLE batches
(
batch_id INT NOT NULL,
cust_id INT NOT NULL,
PRIMARY KEY (batch_id,cust_id),
UNIQUE KEY (cust_id,batch_id)
) ENGINE=InnoDB;
Let's create the customer
, batch_sequence
and batches
tables
mysql> DROP DATABASE IF EXISTS ganeshbora;
Query OK, 3 rows affected (0.40 sec)
mysql> CREATE DATABASE ganeshbora;
Query OK, 1 row affected (0.01 sec)
mysql> USE ganeshbora
Database changed
mysql> CREATE TABLE customer
-> (
-> cust_id INT NOT NULL AUTO_INCREMENT,
-> firstname VARCHAR(20),
-> lastname VARCHAR(20),
-> address VARCHAR(40),
-> PRIMARY KEY (cust_id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)
mysql> CREATE TABLE batch_sequence
-> (
-> batch_id INT NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (batch_id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE batches
-> (
-> batch_id INT NOT NULL,
-> cust_id INT NOT NULL,
-> PRIMARY KEY (batch_id,cust_id),
-> UNIQUE KEY (cust_id,batch_id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)
Let's load the customer
table
mysql> INSERT INTO customer (firstname,lastname,address) VALUES
-> ('william','Faulkner','UK'), ('suzy','nicholus','US'),
-> ('ganesh','bora','india'), ('mahadev','patil','india'),
-> ('prasad','k','UK'), ('sandeep','g','US'),
-> ('prajakta','J','india'), ('Nimish','G','india');
Query OK, 8 rows affected (0.04 sec)
Records: 8 Duplicates: 0 Warnings: 0
Let's load the first batch
mysql> LOCK TABLES batch_sequence WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO batch_sequence VALUES ();
Query OK, 1 row affected (0.00 sec)
mysql> SET @next_batch_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE batch_sequence ENGINE=MyISAM;
Query OK, 1 row affected (0.15 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO batches (batch_id,cust_id) VALUES
-> (@next_batch_id,1),(@next_batch_id,2),(@next_batch_id,3),(@next_batch_id,4);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
Let's load the second batch
mysql> LOCK TABLES batch_sequence WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO batch_sequence VALUES ();
Query OK, 1 row affected (0.00 sec)
mysql> SET @next_batch_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE batch_sequence ENGINE=MyISAM;
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO batches (batch_id,cust_id) VALUES
-> (@next_batch_id,5),(@next_batch_id,6),(@next_batch_id,7),(@next_batch_id,8);
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
Let's see what these table contain
mysql> SELECT * FROM customer;
+---------+-----------+----------+---------+
| cust_id | firstname | lastname | address |
+---------+-----------+----------+---------+
| 1 | william | Faulkner | UK |
| 2 | suzy | nicholus | US |
| 3 | ganesh | bora | india |
| 4 | mahadev | patil | india |
| 5 | prasad | k | UK |
| 6 | sandeep | g | US |
| 7 | prajakta | J | india |
| 8 | Nimish | G | india |
+---------+-----------+----------+---------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM batches;
+----------+---------+
| batch_id | cust_id |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 5 |
| 2 | 6 |
| 2 | 7 |
| 2 | 8 |
+----------+---------+
8 rows in set (0.00 sec)
Let's join them to produce your desired output
mysql> SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);
+---------+-----------+----------+---------+----------+
| cust_id | firstname | lastname | address | batch_id |
+---------+-----------+----------+---------+----------+
| 1 | william | Faulkner | UK | 1 |
| 2 | suzy | nicholus | US | 1 |
| 3 | ganesh | bora | india | 1 |
| 4 | mahadev | patil | india | 1 |
| 5 | prasad | k | UK | 2 |
| 6 | sandeep | g | US | 2 |
| 7 | prajakta | J | india | 2 |
| 8 | Nimish | G | india | 2 |
+---------+-----------+----------+---------+----------+
8 rows in set (0.00 sec)
Let's copy batch 1 into a new batch
mysql> LOCK TABLES batch_sequence WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO batch_sequence VALUES ();
Query OK, 1 row affected (0.00 sec)
mysql> SET @next_batch_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE batch_sequence ENGINE=MyISAM;
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO batches (batch_id,cust_id)
-> SELECT @next_batch_id,cust_id FROM batches WHERE batch_id = 1;
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
Let's join them again to produce your desired output
mysql> SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);
+---------+-----------+----------+---------+----------+
| cust_id | firstname | lastname | address | batch_id |
+---------+-----------+----------+---------+----------+
| 1 | william | Faulkner | UK | 1 |
| 2 | suzy | nicholus | US | 1 |
| 3 | ganesh | bora | india | 1 |
| 4 | mahadev | patil | india | 1 |
| 5 | prasad | k | UK | 2 |
| 6 | sandeep | g | US | 2 |
| 7 | prajakta | J | india | 2 |
| 8 | Nimish | G | india | 2 |
| 1 | william | Faulkner | UK | 3 |
| 2 | suzy | nicholus | US | 3 |
| 3 | ganesh | bora | india | 3 |
| 4 | mahadev | patil | india | 3 |
+---------+-----------+----------+---------+----------+
12 rows in set (0.00 sec)
mysql>
The only thing my answer does differently from what you want is generate batch numbers sequentially by 1.
Please note that I made the a third table called batch_sequence
. The purpose of this table is just to generate batch ids. I treat the batch_sequence
table like and Oracle or PostgreSQL Sequence Object. I prefer making this table MyISAM to ensure a full table lock when creating a new sequence number.
Please also note that LAST_INSERT_ID() is used to retrieve whatever the next sequence number is after an INSERT
into the batch_sequence
table.
What are the Advantages ?
- You do not need to duplicate names and addresses
- Represent batches with smaller amount of data
- Guarantee uniqueness of batch ids by serialization
- Multiple processes can add batches without batch_id collision
Conclusion
If you decide to go with this, the stored procedure should be using this
LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id)
SELECT @next_batch_id,cust_id FROM batches WHERE batch_id = (whatever batch id);
If you want to cut-and-paste all the sample code above, here it is
DROP DATABASE IF EXISTS ganeshbora;
CREATE DATABASE ganeshbora;
USE ganeshbora
CREATE TABLE customer
(
cust_id INT NOT NULL AUTO_INCREMENT,
firstname VARCHAR(20),
lastname VARCHAR(20),
address VARCHAR(40),
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
CREATE TABLE batch_sequence
(
batch_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (batch_id)
) ENGINE=MyISAM;
CREATE TABLE batches
(
batch_id INT NOT NULL,
cust_id INT NOT NULL,
PRIMARY KEY (batch_id,cust_id),
UNIQUE KEY (cust_id,batch_id)
) ENGINE=InnoDB;
INSERT INTO customer (firstname,lastname,address) VALUES
('william','Faulkner','UK'), ('suzy','nicholus','US'),
('ganesh','bora','india'), ('mahadev','patil','india'),
('prasad','k','UK'), ('sandeep','g','US'),
('prajakta','J','india'), ('Nimish','G','india');
LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id) VALUES
(@next_batch_id,1),(@next_batch_id,2),(@next_batch_id,3),(@next_batch_id,4);
LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id) VALUES
(@next_batch_id,5),(@next_batch_id,6),(@next_batch_id,7),(@next_batch_id,8);
SELECT * FROM customer;
SELECT * FROM batches;
SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);
LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id)
SELECT @next_batch_id,cust_id FROM batches WHERE batch_id = 1;
SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);
id
in the list. – Rick James Jul 12 '17 at 13:37AUTO_INCREMENT
to create new ids, don't specify it when inserting. – Rick James Jul 13 '17 at 6:03var_batch_no
and how is it populated/set? Where is theWHERE
clause that designates the rows to be copied (WHERE batch_no = 1
in this case)? I suggest you update your question to include a Minimal, Complete and Verifiable example – markp-fuso Jul 18 '17 at 13:30