CODEWITHSUNDEEP
mysqlsql
Ashwin Yaprala
Ashwin Yaprala

Reputation: 2777

Subquery returns more than 1 row, Error 1242

I am very new to MySQL, can you guys please help me in this?

Query:

UPDATE users u,
       posts p
SET    u.tags = (SELECT group_concat(Tags)
                 FROM   (SELECT DISTINCT Tags,
                                         user_id
                         FROM   posts
                         GROUP  BY tags,
                                   user_id) AS p
                 GROUP  BY user_id)
WHERE  u.user_id = p.user_id;

Error: 

ERROR 1242 (21000): Subquery returns more than 1 row

Upvotes: 0

Views: 1041

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You need a correlated subquery:

update users u
   set u.tags =(select group_concat(Distinct Tags)
                from posts p
                where p.user_id = u.user_id
                group by user_id )

I'm not sure what your query is doing. It has a join on the outside that is unnecessary. It is grouping by tags and user_id on the inside, with an unnecessary distinct keyword. The subquery is producing a different value for each user_id, so it is no wonder that you are getting an error of too many rows returned. A set statement can only have one value.

Upvotes: 1

Related Questions