CODEWITHSUNDEEP
mysqlmove
xander
xander

Reputation: 119

MYSQL move some values to new column depending on uniqueness

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:

enter image description here

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:

enter image description here

Can you help point me in the right direction?

Upvotes: 1

Views: 67

Answers (1)

Timekiller
Timekiller

Reputation: 3126

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.

Upvotes: 1

Related Questions