CODEWITHSUNDEEP
sqlmysql
Shayan
Shayan

Reputation: 2828

SQL Concat Query

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?

Upvotes: 1

Views: 184

Answers (1)

Mark Byers
Mark Byers

Reputation: 838006

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, '');

Upvotes: 2

Related Questions