I have a simple table ItemType, ProductID, and ProductName.

Unfortunately the order of the records is important as ItemType='SKU' is related to the ItemType='Product' above it.

I need to create a new field that associates the 'SKU' record with the 'Product' record above it. What I have is;

What I want is;

Basically I am trying to use the order of the records in the table to build a relationship between parent/child.

I am working with an eCommerce product output file, and it is a dreadful format that I cannot change.

Reading around I think I have to use cursors in MySQL to achieve what I want but I have never used them before.

Any advice is much appreciated!

CodePudding user response：

Please note that this may not be 100% reliable as the order in which rows is returned should not be relied upon. It worked in my test case. It would be much better, and safer, to push for the output file format of the eCommerce product to be updated or to get read only access to the data so you can export it yourself.

I created a table items based on your example above -

CREATE TABLE `items` (
    `ItemType` ENUM('Product', 'SKU') NOT NULL,
    `ProductID` INTEGER NOT NULL,
    `ProductName` VARCHAR(30) NOT NULL
);
INSERT INTO `items` VALUES
    ('Product', 2000, 'My Product Name'),
    ('SKU', 195, 'Red'),
    ('SKU', 207, 'Blue'),
    ('SKU', 124, 'Red'),
    ('Product', 2005, 'My Other Product'),
    ('SKU', 975, 'Red'),
    ('SKU', 127, 'Blue');

It is really important that ProductID is not set as the PK at this point, as it would cause the rows to be re-ordered by ProductID.

Add the new Parent column -

ALTER TABLE `items`
    ADD COLUMN `Parent` INTEGER DEFAULT NULL AFTER `ItemType`;

You can create a query to track the current ProductID using a user variable -

SELECT
    `ProductID`,
    IF(`ItemType` = 'Product', @cur_ID := `ProductID`, NULL) AS `cur_parent_id`,
    IF(`ItemType` = 'SKU', @cur_ID, NULL) AS `parent_id`
FROM `items`, (SELECT @cur_ID := NULL) `p`

Then use the following query to update the Parent column -

UPDATE `items`
INNER JOIN (
    SELECT
        `ProductID`,
        IF(`ItemType` = 'Product', @cur_ID := `ProductID`, NULL) AS `cur_parent_id`,
        IF(`ItemType` = 'SKU', @cur_ID, NULL) AS `parent_id`
    FROM `items`, (SELECT @cur_ID := NULL) `p`
) t ON `items`.`ProductID` = `t`.`ProductID`
SET `items`.`Parent` = `t`.`parent_id`;