Search code examples
sqlmysqlmariadbsql-update

Select + update query in the same table

I am using MariaDB and I am trying to create a query that updates the boolean column is_popular of my photos table, called Pics.

To find out which photos are "popular", I pick the top 10 from the entire table. Then, I would like those top 10 photos to set as true the is_popular field.

The way I could think of to do it was like this:

UPDATE Pics 
SET is_popular=true 
WHERE id=(
   SELECT p.id 
   FROM Pics p 
   WHERE p.approved=true 
   ORDER BY p.points 
   DESC LIMIT 10);

But I get an error like this: ERROR 1242 (21000): Subquery returns more than 1 row

How should I create the query?

Solution

  • Here is a way to do it using INNER JOIN :

    UPDATE Pics p
INNER JOIN (
   SELECT id 
   FROM Pics
   WHERE approved=true 
   ORDER BY points DESC 
   LIMIT 10
) as s on s.id = p.id
SET is_popular=true;

    Demo here