In MySQL how do I update a batch of fields to concatenate their values with those that correspond to the entity_id in the same table?

Question

I hope I'm explaining this clearly enough: In our Magento database I have the following two queries I somehow need to combine (I'm a SQL novice):

update catalog_product_entity_text 
set `value` = CONCAT('', `value`)
where entity_id in (select product_id from catalog_category_product where category_id = 975)
and attribute_id = 57;

select `value` from catalog_product_entity_text 
where entity_id in (select product_id from catalog_category_product where category_id = 571) 
and attribute_id = 551;

The blank value in the CONCAT function in the first query needs to contain the value from the second query that corresponds to the current entity_id in the loop.

Just how do go about I achieving this? Thanks much.

UPDATE: I'm reading up on joins and it looks as though a left outer join on the same table would be the way, though I'm still rather unclear as to the proper syntax and utilization.

Here's a rough draft that is not even passable:

select `value` from catalog_product_entity_text
    as mobile_description
where attribute_id = 551
    and entity_id in (select product_id
        from catalog_category_product
        where category_id = 571)
join on entity_id;

How do I improve upon that to make it effective?

I'm trying to join the rows with attribute_id = 57 & attribute_id = 551, fetching the value column from each, for those with the same entity_id, so that I may update the value of the row with attribute_id = 57 to include the value of the corresponding record with attribute_id = 551.

score 0 · Answer 1 · answered Dec 12 '17 at 13:06

I would say that something like that should work. Of course, first backup database. This is pure SQL question.

update catalog_product_entity_text 
set `value` = CONCAT((select `value` from catalog_product_entity_text 
where entity_id in (select product_id from catalog_category_product where category_id = 571) and attribute_id = 551), `value`)
where entity_id in (select product_id from catalog_category_product where category_id = 975)
and attribute_id = 57;

If it does not work let me know but you can have a quick look at this answer

In MySQL how do I update a batch of fields to concatenate their values with those that correspond to the entity_id in the same table?

1 Answers1