Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-31
11
Medium Priority
?
277 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:davideo7
10 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 37058790
See attached.
query.txt
0
 

Author Comment

by:davideo7
ID: 37059868
awking00: That gave me this error:
#1248 - Every derived table must have its own alias
0
 
LVL 2

Expert Comment

by:jimmycdinata
ID: 37060518
DELETE FROM notifications    
WHERE notification_id NOT IN (
SELECT notification_id FROM notifications WHERE userid=1 ORDER BY date DESC LIMIT 100
);
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37060907
- 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
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 37063663
This time with aliases.
query.txt
0
 

Author Comment

by:davideo7
ID: 37070286
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 2000 total points
ID: 37070687
What happens if you remove the 'as x'?
0
 

Author Comment

by:davideo7
ID: 37070869
awking00: That worked.  Now what if I wanted to do that for every user in 1 single query?
0
 
LVL 72

Expert Comment

by:Qlemo
ID: 37338293
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
 

Author Comment

by:davideo7
ID: 37338294
I'll pick a right answer.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question