How can I delete all rows that don't get selected in this MySQL Query?

I have a MySQL query that looks like this:
SELECT * FROM notifications WHERE userid=1 ORDER BY date DESC LIMIT 100;

How would I create a query that deletes all the rows beyond that 100 limit in the above query?  So rows 1-100 I want to display and rows 100+ I want to delete.  It doesn't have to be all in the same query.
davideo7Asked:
Who is Participating?
 
awking00Commented:
This time with aliases.
query.txt
0
 
awking00Commented:
See attached.
query.txt
0
 
davideo7Author Commented:
awking00: That gave me this error:
#1248 - Every derived table must have its own alias
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
jimmycdinataCommented:
DELETE FROM notifications    
WHERE notification_id NOT IN (
SELECT notification_id FROM notifications WHERE userid=1 ORDER BY date DESC LIMIT 100
);
0
 
OP_ZaharinCommented:
- the above will delete everything more than the 100 limit regardless of userid. add userid = 1 to the query. i assume notification_id  is the unique id in notifications table? then try the following:

DELETE FROM notifications    
WHERE userid=1 AND notification_id NOT IN
(SELECT notification_id FROM notifications WHERE userid=1 ORDER BY date DESC LIMIT 100);

- you can test to see if the data to be deleted is correct by query first:

SELECT * FROM notifications    
WHERE userid=1 AND notification_id NOT IN
(SELECT notification_id FROM notifications WHERE userid=1 ORDER BY date DESC LIMIT 100);
0
 
davideo7Author Commented:
OP_Zaharin: I get this error with that:
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

awking00: I get this error with that:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as x
AND userid = 1' at line 6
0
 
awking00Commented:
What happens if you remove the 'as x'?
0
 
davideo7Author Commented:
awking00: That worked.  Now what if I wanted to do that for every user in 1 single query?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
davideo7Author Commented:
I'll pick a right answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.