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

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.

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

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

TblPartes

TblPartes(desired result)

Thanks.

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