mysqlconcat-ws

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.

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.


Solution

  • 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