Search code examples
mysqlmysql-error-1242

Mysql Query - Subquery returns more than 1 row error

I have a table resources (title, resourceid) and another table classifications (title, resourceid, classificationid)

I want to add the tiles from resources to the classification table, there are multiple rows with same resourceid in classifications.

When I wrote this query 

update `classification` 
set `title`= (select title 
              from Resources 
              where Resources.`resourceid` = classification.`resourceid`)

I got this error:

Error - subquery returns more than 1 row.

Solution

  • Try grouping the subquery by title, i.e.:

    UPDATE classification SET title=(SELECT title FROM Resources
    WHERE Resources.resourceid=classification.resourceid GROUP BY title);