mysql update table column from another table with CONCAT

Question

I am trying to work out how to perform an SQL UPDATE table column from Table A into Table B. The problem I have is trying to concat multiple values from Table A Column X into Table B Column Y

TableA Structure id | Interest 1 | Bowling 2 | Swimming 1 | Basketball

TableB Structure id | Interest_new 1 | null 2 | null

I want Table B to have following data

TableB id | Interest_new 1 | Bowling,Basketball 2 | Swimming

This is my attempt with SQL query but it doesn't concat , just updated table B with first match

    UPDATE TableB 
INNER JOIN TableA ON TableB.id= TableA.id
SET TableB.id=CONCAT(TableA.id, ',')
where TableA.id= TableB.id;

Tim Biegeleisen · Accepted Answer · 2021-03-01 04:03:43Z

2

You probably intend to use GROUP_CONCAT here, as you want an aggregated CSV output:

UPDATE TableB b
INNER JOIN
(
    SELECT id, GROUP_CONCAT(Interest ORDER BY id) Interests
    FROM TableA
    GROUP BY id
) a
    ON a.id = b.id
SET
    Interest_new = a.Interests;

However, I actually advocate not even doing this, as storing CSV in your SQL tables is a generally bad idea. Consider just making a view of this data instead:

CREATE VIEW newInterests AS
SELECT id, GROUP_CONCAT(Interest ORDER BY id) Interests
FROM TableA
GROUP BY id;

answered Mar 1, 2021 at 4:03

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql update table column from another table with CONCAT

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related