[RESOLVED] LIMIT question!

Zpixel

how can this line of code be interpreted?

mysql_query("DELETE FROM news where(update_time<$time) LIMIT 500");

1) search within the first 500 records of the news table and delete news where(update_time<$time)

2) delete 500 records throughout the news table where(update_time<$time)

laserlight

The answer is #2.

Zpixel

thank you.

for the first option can we write such a thing?

mysql_query("DELETE FROM news where(update_time<$time) LIMIT 0,500");

bradgrafelman

I would think that you'd have to use a sub-query for that. Assuming that you have an AUTO_INCREMENT column named 'id' to keep track of the rows, you might try something like:

DELETE FROM news
    WHERE id IN (SELECT id FROM news ORDER BY id ASC LIMIT 500)
    AND update_time < $time

Zpixel

it's interesting. can you introduce me a reference to show me more of these compound queries. i mean like this :

DELETE FROM news WHERE id IN (SELECT id FROM news ORDER BY id ASC LIMIT 500)

bradgrafelman

Like I said, I didn't test the query or anything, so I'm not even saying it'll work as expected (unless you've tried it, perhaps?).

Anyway, the manual page for that syntax is here. MySQL basically performs the subquery to get a list of ID's and treats that list like it would any other list for the IN() statement.

Zpixel

it was a good reference. thank you.

Weedpacket

Well, it's the official manual; what would you expect?

Zpixel

nothing more, except something to learn. i like to know more about eregi functions, joining mysql tables and its usage , ..

i think you and those who answer to the applicants questions have a big list of good links and resources to spend one of them in time of need. will you share more of your treasury ..