Question 290480 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Both MySQL and MariaDB support ORDER BY and LIMIT clauses for single-table updates. So you can write your query just as simple as:

    UPDATE Pics 
SET is_popular = true 
WHERE approved = true 
ORDER BY points DESC
LIMIT 10
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search