CODEWITHSUNDEEP
mysql
Torben
Torben

Reputation: 5494

MySQL - copy or update rows with a change within one table

I have a database table like this one:

group | detailsID | price
EK    |         1 |  1.40
EK    |         2 |  1.50
EK    |         3 |  1.60
H     |         1 |  2.40
H     |         2 |  2.50

Now I want to copy the data from group "EK" to the group "H", so the prices for the detailsID's must be adjusted for the detailsIDs 1 and 2, and the entry for detailsID 3 must be inserted for group "H".

How can I do that with one or two MySQL query's?

Thanks!

Upvotes: 0

Views: 865

Answers (2)

Mangesh Sathe
Mangesh Sathe

Reputation: 2177

You can try this as well, Following code implemented using stored procedures. Very simple not that difficult to understand. You may need to modify data type and optimize the code as per the requirement.

DELIMITER $$;

DROP PROCEDURE IF EXISTS update_H $$;

CREATE PROCEDURE update_H()
BEGIN

DECLARE finished INTEGER DEFAULT 0;
DECLARE `group_col` varchar(255) DEFAULT "";
DECLARE `detaildid_col` varchar(255) DEFAULT "";
DECLARE `price_col` varchar(255) DEFAULT "";

DECLARE  H_FOUND INTEGER DEFAULT 0;

DECLARE pull_data CURSOR FOR select `group`, `detaildid`, `price` from test.newtab WHERE `group` = 'EK';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;


OPEN pull_data;
traverse_data: LOOP

FETCH pull_data INTO group_col, detaildid_col, price_col;
IF finished = 1 THEN 
LEAVE  traverse_data;
END IF;

SET H_FOUND = (SELECT count(*) from test.newtab where `group` = 'H' AND `detaildid` = detaildid_col);

IF ( H_FOUND = 1 ) THEN
        UPDATE  test.newtab SET `price` = price_col where `group` = 'H' AND `detaildid` = detaildid_col;
ELSE
    INSERT INTO test.newtab (`group`, `detaildid`, `price`)  VALUES ('H', detaildid_col, price_col); 
END IF;

END LOOP traverse_data;
CLOSE pull_data;

END $$;
DELIMITER ;

You can call this procedure by executing, call update_H();

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

We can try doing an INSERT INTO ... SELECT with ON DUPLICATE KEY UPDATE:

INSERT INTO yourTable (`group`, detailsID, price)
SELECT 'H', detailsID, price
FROM yourTable t
WHERE `group` = 'EK'
ON DUPLICATE KEY UPDATE price = t.price;

But this assumes that there exists a unique key on (group, detailsID). If this would not be possible, then this approach would not work.

As an alternative, I might do this in two steps. First, remove the H group records, then insert the updated H records you expect.

DELETE
FROM yourTable
WHERE `group` = 'H';

INSERT INTO yourTable (`group`, detailsID, price)
SELECT 'H', detailsID, price
FROM yourTable
WHERE `group` = 'EK';

I use the above approach because a single update can't handle your requirement, since new records also need to be inserted.

Note that you should avoid naming your columns and tables using reserved MySQL keywords such as GROUP.

Upvotes: 2

Related Questions