MYSQL move some values to new column depending on uniqueness

Question

I have a list of products, product options (eg size) and option values (eg S/M/L/XL) all stored in a single table per the image below:

This works fine when a product has only one option set (eg size). As you will see product ID 53 has two types of options, being size (option_id 11)and colour (option_id 13).

Further in the database I also have products that can have up to three types of options, and I'm trying to write a script that accommodates these variances as I'm trying to migrate my online store to another system which is pretty specific about the format they receive product data in.

I am struggling to figure out the right way to approach this problem and write the UPDATE statement in a way that moves the 2nd and 3rd options/value sets into the appropriate 2nd and 3rd columns (option_2_name, option_2_value_name and option_3_name, option_3_value_name respectively, if they of course exist per product).

To help visually, here's another image that shows where I want (in this case) the size options/values for product 53 to move to:

Can you help point me in the right direction?

This looks like a pretty disastrous table architecture. Do you really need a denormalized table? The format you're trying to achieve is pretty strange too, describe the desired end result maybe? — Timekiller, Dec 14 '15 at 12:26
Hi @Timekiller tell me about it. I'm moving to Shopify, and I'm trying to conform to their bulk product import (CSV) file. Essentially I need to list each option/value pair on a single row, and repeat until they are exhausted. Where I have the need for additional option/value pairs I need to shift these to the option 2 name/value columns as per the diagram above. HTH :) — xander, Dec 14 '15 at 12:43
@Timekiller I should add this is a working table - I have a cleaner (final) version that I intend to consolidate this data on once I have the rows organised properly. — xander, Dec 14 '15 at 12:45

score 1 · Accepted Answer · answered Dec 14 '15 at 13:10

Well, like I said, it's not a normal table structure, so operations on it are going to be weird, and I'm still questioning this approach. But here's something you can use to move to column 2:

update table_name t1
set option_2_name = option_1_name, option_2_value=option_1_value,
    option_1_name=NULL, option_1_value=NULL
where 
(select count(distinct option_id) 
   from table_name t2 
  where t1.product_id = t2.product_id 
    and t1.option_id > t2.option_id) = 1

and to column 3:

update table_name t1
set option_3_name = option_1_name, option_3_value=option_1_value,
    option_1_name=NULL, option_1_value=NULL
where 
(select count(distinct option_id) 
   from table_name t2 
  where t1.product_id = t2.product_id 
    and t1.option_id > t2.option_id) = 2

And so on you you will need more.

That will move higher option ids further. I didn't test it, but it should run fine.

Thanks, your code above was almost perfect. I received an error 1093 as you cannot edit a table you are using the SELECT command on. I fixed it by duplicating the table above temporarily, then did the comparison across tables, modifying the original table (t1) and dropping t2 once I was finished with it. — xander, Dec 14 '15 at 22:47

MYSQL move some values to new column depending on uniqueness

1 Answers1