CODEWITHSUNDEEP
mysqlsql-update
JoeDoe
JoeDoe

Reputation: 47

MySQL update select from same table

How can I update column for the same table with this query:

update products 
set (related_product_id) = 
(
select GROUP_CONCAT(id) from products
INNER JOIN products_cross on products_cross.product_upc = products.upc
WHERE products_cross.related_product_upc = 2631695
)

PRODUCTS table before:

id   |   upc   |   related_product_id  |
3721 | 2631695 |                       |
4566 | 37262   |                       |
3723 | 173615  |                       |
3724 | 216571  |                       |

PRODUCTS table after:

id   |   upc   |   related_product_id  |
3721 | 2631695 |  4566,3723,3724       |
4566 | 37262   |                       |
3723 | 173615  |                       |
3724 | 216571  |                       |

PRODUCTS CROSS table:

product_upc | related_product_upc |   
37262       | 2631695             |  
173615      | 2631695             |
216571      | 2631695             |

Upvotes: 0

Views: 1418

Answers (2)

Priyesh
Priyesh

Reputation: 541

Another way to do as below example:

Use the mysql local variables 

SELECT 
  @product_id_list_comma_separated := GROUP_CONCAT(table1.id) 
FROM
  products AS table1 
  INNER JOIN products_cross 
    ON products_cross.product_upc = table1.upc ;

UPDATE products AS table2 SET table2.related_product_id = @product_id_list_comma_separated;

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Might be something like below 

update products p1
join (
   select c.related_product_upc ,GROUP_CONCAT(product_upc) related_products
   from products p
   join products_cross c on c.related_product_upc = p.upc
   where c.related_product_upc = 2631695
   group by c.related_product_upc 
) t on( t.related_product_upc = p1.upc)
set p1.related_product_id = t.related_products;

DEMO

Note: storing related products as a comma separated values is a bad design instead you can create a new table and relate your products so that each relation will be stored as a single row

related_products

product_id related_product_id
3721        4566
3721        3723
3721        3724

Upvotes: 1

Related Questions