SQL Concat Query

Question

I have two tables like this:

TABLE user(
id     CHAR(100)
text   TEXT
)

TABLE post(
postid     CHAR(100)
postedby   CHAR(100)
text       TEXT
FOREIGN KEY (postedby) references user
);

I need a query that for each user concatenates the TEXT column of all posts of that user and put them in the text column of the user. the order is not important. What should I do?

Answer 1

To select the values use GROUP_CONCAT:

SELECT postedby, GROUP_CONCAT(text)
FROM post
GROUP BY postedby

To update your original table you will need to join this result with your original table using a multi-table update.

UPDATE user
LEFT JOIN
(
    SELECT postedby, GROUP_CONCAT(text) AS text
    FROM post
    GROUP BY postedby
) T1
ON user.id = T1.postedby
SET user.text = IFNULL(T1.text, '');