MySQL: Flag Random Rows

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

I started with this

MYSQL Code: [ Select ]
UPDATE table
   SET flag =
      CASE
         WHEN id IN (SELECT id FROM table ORDER BY RAND() LIMIT 3) THEN 1
         ELSE 0
      END;
  1. UPDATE table
  2.    SET flag =
  3.       CASE
  4.          WHEN id IN (SELECT id FROM table ORDER BY RAND() LIMIT 3) THEN 1
  5.          ELSE 0
  6.       END;


which lead to this error

Code: [ Select ]
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'


so I pulled out the LIMIT and was presented with this error

Code: [ Select ]
ERROR 1093 (HY000): You can't specify target table 'table' for update in FROM clause


Note this table will likely never have more than 50 rows in it at a time and certainly never more than a couple of hundred in an extreme case, so I'm not worried about the "ORDER BY RAND()" and any performance issues.

Basically I'm trying to flag N rows after de-flagging all rows with one query so I can setup a cron job to do it every day.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

After finding this comment by Are you mortal on the MySQL manual pages along with this article by Xaprb I came up with this, which seems to work so far for my fairly tiny table.

MYSQL Code: [ Select ]
UPDATE table1
   SET flag = CASE WHEN id IN
      (
         SELECT id FROM
         (
            SELECT *
               FROM table1 AS table2
               ORDER BY RAND()
               LIMIT 3
         ) AS table3
      )
      THEN 1
      ELSE 0
   END;
  1. UPDATE table1
  2.    SET flag = CASE WHEN id IN
  3.       (
  4.          SELECT id FROM
  5.          (
  6.             SELECT *
  7.                FROM table1 AS table2
  8.                ORDER BY RAND()
  9.                LIMIT 3
  10.          ) AS table3
  11.       )
  12.       THEN 1
  13.       ELSE 0
  14.    END;
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

I knew you were going to talk to yourself as soon as I saw your post and failed to find anything on Google :lol:

What exactly are flags? The MySQL reference manual doesn't seem to tell me... or I can't find the right article there...
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

Flags aren't anything to do with MySQL exactly, I just happen to have a column on this table that I use as a true/false flag to tell the script whether to include each row when doing something or not.

In this particular case, I'm flagging rows in a category table to be included as "featured" categories on the site index. In my case the flag column is actually named "soi" for "show on index", but flag seemed better for starting the topic with. :)
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

So... SET flag ... means SET column_flag ?

The flag is a column? Not a MySQL keyword? Like CREATE TRIGGER?
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

Exactly, though now you have me wanting to check the manual to make sure FLAG isn't a reserved word. :scratchhead:

Code: [ Select ]
mysql> help flag;

Nothing found
Please try to run 'help contents' for a list of all accessible topics
  1. mysql> help flag;
  2. Nothing found
  3. Please try to run 'help contents' for a list of all accessible topics


Nope, looks like I'm safe. :D
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

Alright than... I guess I was searching for the wrong stuff, and so I couldn't have being able to help you anyway :lol:

Glad you got it figured out and I'm happy that I learned something useful :) Thanks

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 146 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.