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

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

Using django-mptt to get only the categories that have items -