CODEWITHSUNDEEP
mysqldatabaseselectinsert
klye_g
klye_g

Reputation: 1242

MySQL Split Single Row Values into Multiple Inserts

Here's my current order sales table structure (simplified):

id|order_id|fee1|fee2|fee3|fee4|fee5|fee6|fee7|fee8|total|created

Here's what I would like it to become:

Transactions:

id|order_id|total|created

Transaction_fee:

id|transaction_id|fee|amount

How would I go about taking the original order sales records, and moving them over to this new 2-table structure?

I know of the INSERT...SELECT for MySQL, and this is what I have so far:

INSERT INTO transactions (order_id, total, created)
      SELECT order_id, total, created
      FROM order_sales

But I also need to grab fee1...fee(n) and put those into transaction_fee table, preferably in the same query as the fee table will have the transactions primary key as a foreign key, and thus needs to be accurate.

So it becomes:

Transactions
1|123|50.00|2015-01-01 00:00:00

Transaction_fee
1|1|fee1|10.00
1|1|fee2|10.00
1|1|fee3|10.00
1|1|fee4|10.00
1|1|fee5|10.00

Any thoughts?

Upvotes: 2

Views: 728

Answers (3)

sn00k4h
sn00k4h

Reputation: 443

If 'order_id' column is unique:

INSERT INTO Transaction_fee(transaction_id, fee, amount)
  SELECT t.id, 'fee1', o.fee1
  FROM Transactions t
    LEFT JOIN Order o USING(order_id) -- Assuming your current table is called 'Order'
  UNION ALL
  SELECT t.id, 'fee2', o.fee2
  FROM Transactions t
    LEFT JOIN Order o USING(order_id)
  UNION ALL
  ...

If order_id isn't unique, you can still implement the same method, but using your existing order table's 'id' column. What I'd do is, temporarily include this 'id' column as the last column of the new 'Transactions' table, use it to do the join, and after you finish migrating data to your Transactions_fee table, drop the temporary 'Transactions.id' column.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

Consider the following...

DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,order_id INT NOT NULL
,fee1 DECIMAL(5,2) NOT NULL
,fee2 DECIMAL(5,2) NOT NULL
,fee3 DECIMAL(5,2) NOT NULL
,total DECIMAL(5,2) NOT NULL
,created DATETIME NOT NULL
);

INSERT INTO transactions VALUES 
(1,123,50.00,75.00,25.00,150.00,'2015-01-01 00:00:00'),
(2,125,10.00,25.00,15.00,50.00,'2015-01-02 00:00:00');


SELECT * FROM transactions;

+----+----------+-------+-------+-------+--------+---------------------+
| id | order_id | fee1  | fee2  | fee3  | total  | created             |
+----+----------+-------+-------+-------+--------+---------------------+
|  1 |      123 | 50.00 | 75.00 | 25.00 | 150.00 | 2015-01-01 00:00:00 |
|  2 |      125 | 10.00 | 25.00 | 15.00 |  50.00 | 2015-01-02 00:00:00 |
+----+----------+-------+-------+-------+--------+---------------------+

SELECT id
     , order_id
     , 1 fee_id
     , fee1 value
     , created 
  FROM transactions
 UNION
SELECT id
     , order_id
     , 2 
     , fee1 
     , created 
  FROM transactions
 UNION
SELECT id
     , order_id
     , 3 
     , fee1 
     , created 
  FROM transactions;
+----+----------+--------+-------+---------------------+
| id | order_id | fee_id | value | created             |
+----+----------+--------+-------+---------------------+
|  1 |      123 |      1 | 50.00 | 2015-01-01 00:00:00 |
|  2 |      125 |      1 | 10.00 | 2015-01-02 00:00:00 |
|  1 |      123 |      2 | 50.00 | 2015-01-01 00:00:00 |
|  2 |      125 |      2 | 10.00 | 2015-01-02 00:00:00 |
|  1 |      123 |      3 | 50.00 | 2015-01-01 00:00:00 |
|  2 |      125 |      3 | 10.00 | 2015-01-02 00:00:00 |
+----+----------+--------+-------+---------------------+


DROP TABLE IF EXISTS transactions_new;

CREATE TABLE transactions_new AS
SELECT id
     , order_id
     , 1 fee_id
     , fee1 value
     , created 
  FROM transactions
 UNION
SELECT id
     , order_id
     , 2 
     , fee1 
     , created 
  FROM transactions
 UNION
SELECT id
     , order_id
     , 3 
     , fee1 
     , created 
  FROM transactions;

ALTER TABLE transactions_new ADD PRIMARY KEY(id,order_id,fee_id);

SELECT * FROM transactions_new;
+----+----------+--------+-------+---------------------+
| id | order_id | fee_id | value | created             |
+----+----------+--------+-------+---------------------+
|  1 |      123 |      1 | 50.00 | 2015-01-01 00:00:00 |
|  1 |      123 |      2 | 50.00 | 2015-01-01 00:00:00 |
|  1 |      123 |      3 | 50.00 | 2015-01-01 00:00:00 |
|  2 |      125 |      1 | 10.00 | 2015-01-02 00:00:00 |
|  2 |      125 |      2 | 10.00 | 2015-01-02 00:00:00 |
|  2 |      125 |      3 | 10.00 | 2015-01-02 00:00:00 |
+----+----------+--------+-------+---------------------+

Upvotes: 1

Peter Green
Peter Green

Reputation: 137

I know that changing the structure of the database requires many other changes and it could be a lot of work in many cases, however I think changing the structure of your database will make your work much more extendable and straightforward .

For example if you like to add a new kind of fees in the future will require adding a new column and editing many existing queries as well so I recommend splitting your sales table in 3 relates tables : Orders (OrderID, Total, date created) //not sure if the total is necessary it could be selected via a query I am adding it just in case

FeeType (Fee_ID, Fee_Title) //all fees types(fee1,fee2,..etc) should be stored here as records

OrderFees (OrderID, Fees_ID, Amount) //here I am assuming that the paid amount is added each time, if not you can make the "Amount" in the FeeTypes table .

Now if you want to send a transaction it could be a complete insert statement to the Orders table if you just want to save the summery of order otherwise if you want to send details about each fees you will send insert query to the OrderFees Table with the OrderID , FeeID and amount due .

Upvotes: 0

Related Questions