CODEWITHSUNDEEP
mysqlsql
AlFra
AlFra

Reputation: 419

Duplicating table rows, but with another value

I have table product_to_store (it is opencart table), with product_id and store_id as column headers. Table is filled with data (example below):

+------------+----------+
| product_id | store_id |
+------------+----------+
| 123        | 0        |
| 124        | 0        |
| 125        | 0        |
| 126        | 0        |
| 125        | 1        |
+------------+----------+

Help (from mysql table dump): 

CREATE TABLE `product_to_store` (
  `product_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `product_to_store` (`product_id`, `store_id`) VALUES
(123, 0),
(124, 0),
(125, 0),
(126, 0),
(125, 1);

ALTER TABLE `product_to_store`
  ADD PRIMARY KEY (`product_id`,`store_id`);

I would like to duplicate all rows with value 0 as store_id. Instead of value 0, I would like them to have value 1 (for example). How can I do that with using mysql syntax?

So final table should look something like this

+------------+----------+
| product_id | store_id |
+------------+----------+
| 123        | 0        |
| 124        | 0        |
| 125        | 0        |
| 126        | 0        |
| 125        | 1        |
| 123        | 1        |
| 124        | 1        |
| 126        | 1        |
+------------+----------+

Upvotes: 0

Views: 67

Answers (2)

Tenzin
Tenzin

Reputation: 2505

You could make a cursor to retrieve the records you are looking for.
Loop through that cursor and put those rows in a record so that you could modify them.
This way you could do something with every line.

For example: 

DECLARE
    CURSOR cHelloWorld IS 
            SELECT  product_id, 
                    store_id 
                FROM    product_to_store 
                WHERE   store_id = 0; 

    rHelloWorld     cHelloWorld%ROWTYPE;

BEGIN
        --Loop through records from cursor. 
        OPEN cHelloWorld;
        LOOP
                FETCH cHelloWorld INTO rHelloWorld;
                -- Exit loop when all lines have been looked through.
                EXIT WHEN cHelloWorld%NOTFOUND;

        INSERT INTO product_to_store(product_id, store_id)
                VALUES (rHelloWorld.product_id, 1); 
        END LOOP;
        CLOSE cHelloWorld;

        COMMIT;
END;
/

Upvotes: 0

ychepel
ychepel

Reputation: 26

I think this will help you:

insert ignore into `product_to_store` (
  select a.product_id, 1 as store_id
  from (select distinct product_id from product_to_store where store_id=0) a
)

Upvotes: 1

Related Questions