sql - Update MySQL Query that selects records with a certain non-unique value -
i'm looking creating mysql query. have following database table:
+----------------+-----------+-------------+------------+-------------------+---------+-------------+---------------+--------+ | option_type_id | option_id | sku | sort_order | customoptions_qty | default | in_group_id | dependent_ids | weight | +----------------+-----------+-------------+------------+-------------------+---------+-------------+---------------+--------+ | 552 | 137 | 13071727 | 1000 | 0 | 0 | 0 | | 0.0000 | | 553 | 137 | 13071727b | 1000 | 0 | 0 | 1 | | 0.0000 | | 554 | 137 | 13071727c | 1000 | 1 | 0 | 2 | | 0.0000 | | 555 | 137 | 13071727d | 1000 | 1 | 0 | 3 | | 0.0000 | | 556 | 138 | 13085350-1 | 1000 | 0 | 0 | 0 | | 0.0000 | | 557 | 138 | 13085350-1d | 1000 | 2 | 0 | 1 | | 0.0000 | | 558 | 138 | 13085350-1c | 1000 | 3 | 0 | 2 | | 0.0000 | | 559 | 138 | 13085350-1b | 1000 | 2 | 0 | 3 | | 0.0000 | | 560 | 139 | 13069547m | 1000 | 20 | 0 | 0 | | 0.0000 | | 561 | 140 | 13084477-2 | 950 | 2 | 0 | 0 | | 0.0000 | | 562 | 140 | 13084477-2b | 951 | 2 | 0 | 1 | | 0.0000 | | 563 | 140 | 13084477-2c | 952 | 3 | 0 | 2 | | 0.0000 | | 564 | 140 | 13084477-2d | 953 | 0 | 0 | 3 | | 0.0000 | | 565 | 140 | 13084477-2e | 954 | 2 | 0 | 4 | | 0.0000 | | 566 | 141 | 13066533-1 | 1000 | 4 | 0 | 0 | | 0.0000 | | 567 | 141 | 13066533-1b | 1000 | 5 | 0 | 1 | | 0.0000 | | 568 | 141 | 13066533-1c | 1000 | 5 | 0 | 2 | | 0.0000 | | 569 | 141 | 13066533-1d | 1000 | 0 | 0 | 3 | | 0.0000 | | 570 | 142 | 13071674 | 1000 | 0 | 0 | 0 | | 0.0000 | | 571 | 142 | 13071674d | 1000 | 1 | 0 | 1 | | 0.0000 | | 572 | 142 | 13071674c | 1000 | 2 | 0 | 2 | | 0.0000 | | 573 | 142 | 13071674b | 1000 | 0 | 0 | 3 | | 0.0000 | | 574 | 142 | 13071674e | 1000 | 4 | 0 | 4 | | 0.0000 | | 575 | 143 | 13071667 | 1000 | 0 | 0 | 0 | | 0.0000 | | 576 | 143 | 13071667b | 1000 | 0 | 0 | 1 | | 0.0000 | | 577 | 143 | 13071667c | 1000 | 0 | 0 | 2 | | 0.0000 | | 578 | 143 | 13071667d | 1000 | 0 | 0 | 3 | | 0.0000 | | 579 | 143 | 13071667e | 1000 | 0 | 0 | 4 | | 0.0000 | | 580 | 144 | 13066295 | 1000 | 1 | 0 | 0 | | 0.0000 | +----------------+-----------+-------------+------------+-------------------+---------+-------------+---------------+--------+
what i'd update rows in_group_id
= 0, add a
end of sku
. i'd on rows do not have unique option_id
value.
for example, can see row option_id
= 139 only row option_id
value, i'd exclude row being updated.
basically list of product options, products have multiple options each it's own sku. these products have multiple options there 'a' missing end of 1st option's sku, , i'd add it. don't wish add 'a' products have 1 option. hope makes sense.
if advise on mysql statement achieve this, great!
this query want :
update table1 left outer join (select * table1 group option_id having count(*) = 1) t1 on t1.option_type_id = table1.option_type_id set table1.sku = concat(table1.sku,'a') table1.in_group_id = 0 , t1.option_type_id null
see sql fiddle : http://www.sqlfiddle.com/#!2/9d08e/1/0
Comments
Post a Comment