Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why does this query DELETE FROM test WHERE id=(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); sometimes delete 1 row, sometimes 2 rows and sometimes nothing?

If I write it in this form: SET @var = (SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); DELETE FROM test WHERE id=@var;, then it work correctly - is problem in subquery?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

The reason the first query does not work consistently has to do with how MySQL processes subqueries. In fact, subqueries will experience rewrites and transformations.

There are four(4) components explained here:

  • Item_in_optimizer
  • Item_in_subselect
  • Item_ref
  • Left_expression_Cache

From the examples posted, it would be impossible to allow an item_ref to become a self reference. In terms of your single DELETE query, the test table as a whole cannot fully self reference itself because some keys are available during transformation and some are not. Therefore, when a query performs a self-reference, a key (in this case id) can disappear in a transformation even though the actual self-referenced table has the key.

Mysql subqueries are only great for sub-SELECTs, even self-referencing a table multiple times. The same cannot be said for non-SELECT queries.

I hope this explanation helps.

share|improve this answer

I think that the reason why it doesn't work as expected is not how MySQL processes subqueries but how MySQL processes UPDATE statements. The statement:

DELETE 
FROM test 
WHERE id = 
      ( SELECT id 
        FROM 
            ( SELECT * 
              FROM test
            ) temp 
        ORDER BY RAND() 
        LIMIT 1
      ) 

will process the WHERE condition row by row. Meaning, for every row, it will run the subquery and test the result against id:

  ( SELECT id 
    FROM 
        ( SELECT * 
          FROM test
        ) temp 
    ORDER BY RAND() 
    LIMIT 1
  ) 

So, it will occasionally match (and delete) 0, 1, 2 or even more rows!


You could rewrite it like this and the subquery will be processed once:

DELETE t
FROM 
      test t
  JOIN 
      ( SELECT id 
        FROM test  
        ORDER BY RAND() 
        LIMIT 1
      ) tmp
    ON tmp.id = t.id
share|improve this answer

From the first bullet on this page, LIMIT is not supported in mysql subqueries. I'm not sure why it doesn't throw an error for you, though.

share|improve this answer
2  
LIMIT is not supported only for using IN (<code> replaced with backticks ~ drachenstern) –  tomas.lang Feb 21 '11 at 14:05
    
well...I learned something, which explains why it didn't toss an error! –  Derek Downey Feb 21 '11 at 14:18
    
@tomas.lang you can use ` (tick marks) surrounding the word, instead of <code> blocks. –  Derek Downey Feb 21 '11 at 14:19
    
thanks :-) –  tomas.lang Feb 21 '11 at 14:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.