[MySql]How to copy a row from one table to another and fill additional columns?

Question

So I want to condense this query and I'm not sure how to go about it.

Right now I use

$move_to_sold = 
    "INSERT INTO sold_inv_item(
        `sku`,
        `designer`,
        `type`,
        `price`,
        `metal_type`,
        `stone_type`,
        `stone_weight`,
        `item_desc`,
        `item_img`
    )
    SELECT
        `sku`,
        `designer`,
        `type`,
        `price`,
        `metal_type`,
        `stone_type`,
        `stone_weight`,
        `item_desc`,
        `item_img`
    FROM inv_item
    WHERE `sku` = '$sku'";

$add_date_and_name = 
    "UPDATE sold_inv_item 
    SET `purchase_date` = '$date', `customer_name` = '$name' 
    WHERE `sku` = '$sku'";

what I'm trying to do is copy a row from 1 table to another, and then add additional data into the 2 extra columns from $_POST data.

I tried something like

INSERT INTO sold_inv_item(`sku`,`designer`,`type`,`price`,`metal_type`,
`stone_type`,`stone_weight`,`item_desc`,`item_img`,`purchase_date`,`customer_name`) 

VALUES ((select `sku`,`designer`,`type`,`price`,`metal_type`,
`stone_type`,`stone_weight`,`item_desc`,`item_img` FROM inv_item WHERE inv_item.sku = 'pepsi-2-tone'), '2019-02-14','betty Girl');

This gives a column mismatch error, I suspect because it's trying to fit all the rows into 1 column instead of spreading them out.

Finally this works but I'd rather do something like the middle solution if possible.

INSERT INTO sold_inv_item(`sku`,`designer`,`type`,`price`,`metal_type`,
`stone_type`,`stone_weight`,`item_desc`,`item_img`,`purchase_date`,`customer_name`) 

VALUES ((select `sku` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `designer` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `type` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `price` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `metal_type` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `stone_type` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `stone_weight` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `item_desc` from inv_item where inv_item.sku = 'pepsi-2-tone'),
        (select `item_img` from inv_item where inv_item.sku = 'pepsi-2-tone'),'2019-02-14','betty Girl');

So finally the question is, what is the most effective way code and performance wise to copy a row from one table to another and fill additional columns. There a few similar ones but none that I can find cover more than 1-2 columns being copied over.

score 2 · Accepted Answer · answered Feb 06 '19 at 20:36

2

You can use the INSERT ... SELECT syntax, like :

INSERT INTO sold_inv_item (
    `sku`,
    `designer`,
    `type`,
    `price`,
    `metal_type`,
    `stone_type`,
    `stone_weight`,
    `item_desc`,
    `item_img`, 
    `purchase_date`, 
    `customer_name`
)
SELECT 
    `sku`,
    `designer`,
     `type`,
     `price`,
     `metal_type`,
     `stone_type`,
     `stone_weight`,
     `item_desc`,
     `item_img`,
     :date,
     :name
FROM inv_item
WHERE `sku` = :sku

PS : when it comes to using POSTed parameters in SQL queries, anyone on SO will strongly suggest to use prepared statements and parameterized queries, to protect your code from SQL injection and make your queries more efficient, readable and maintainable. I modified the query to use named parameters.

answered Feb 06 '19 at 20:36

GMB

216,147
25
84
135

1

This works, I didn't know you could add variables that didn't exist in the table you're pulling from directly to a select. Thanks! – Purgedmoon Feb 06 '19 at 20:45
Ya, I'm just getting the logic down. I'll convert this to a prepared query when it goes to code. – Purgedmoon Feb 06 '19 at 20:47
@Purgedmoon : exactly. Then you can prepare the statement once, and reuse it multiple times as needed. – GMB Feb 06 '19 at 20:48

[MySql]How to copy a row from one table to another and fill additional columns?

1 Answers1