Is it possible to update a field with the result of mysql concat and join?

Question

Is it possible to update a field with the result of mysql concat and join?

select CONCAT_WS(' - ', r.spare, r.assembly, r.model) 
reemplazos from TblPartes p 
left join TblReemplazos r 
on p.codigo1 = r.Option where p.codigo1 = 'V4-VS07-040'

I get 3 rows of results with multiple columns.

reemplazos
005050748 - 005050148 - 005050749
005050149 - 005050552- 005050953
005052065 - 005052064

but if I use update, the result of the update its only the first row.

005050748 - 005050148 - 005050749

update TblPartes As p 
left JOIN TblReemplazos As r 
On p.codigo1 = r.Option 
Set p.stock_reemp = 
CONCAT_WS(' - ', r.spare, r.assembly, r.model)

How can make the update using the result of the first select? (concat all rows)

I need something like this

update TblPartes As p 
left JOIN TblReemplazos As r 
On p.codigo1 = r.Option 
Set p.stock_reemp = select CONCAT_WS(' - ', r.spare, r.assembly, r.model) 
reemplazos from TblPartes p 
left join TblReemplazos r 
on p.codigo1 = r.Option

TblReemplazos

id	option	spare	assembly	model
1	V4-VS07-040	005050748	005050148	005050749
2	V4-VS07-040	005050149	005050552	005050953
3	V4-VS07-040	005052065	005052064
4	V8-VS08-080	8811uu33	8811uu44	8811uu55

TblPartes

id	codigo1	stock_reemp
1	V4-VS07-040
2	V8-VS08-080

TblPartes(desired result)

id	codigo1	stock_reemp
1	V4-VS07-040	005050748 - 005050148 - 005050749 - 005050149 -005050552 -005050953 - 005052065 - 005052064
2	V8-VS08-080	8811uu33 - 8811uu44 - 8811uu55

Thanks.

Answer 1

You need to use GROUP_CONCAT() to combine the values from multiple rows. This means you need to join with a subquery that uses GROUP_CONCAT().

Also, option shouldn't be in the CONCAT_WS().

update TblPartes AS p
left join (
    SELECT `option`, GROUP_CONCAT(CONCAT_WS(" - ", spare, assembly, model) SEPARATOR ' - ') AS stock_reemp
    FROM TblReemplazos
    GROUP BY `option`
) AS r on p.codigo1 = r.`option`
set p.stock_reemp = r.stock_reemp;

DEMO