Search code examples
mysqlsql-delete

PHP MYSQL Delete certain amount (5) rows

In my DB I will insert a new row when a user referrers someone to my website. When a user earns 5 referral points I want to remove 5 of the inserted rows that were inserted when they referred someone.

So say my db is like this

|id|userID|
|0 |543678|
|1 |543678|
|2 |543678|
|3 |543678|
|4 |543678|
|5 |543678|

there is a total of 6 inserted rows which represent 6 referrals. So how can I remove (delete) only 5 of these rows and keep 1 remaining? (btw I am working with PHP)

Solution

  • Use a LIMIT clause in the DELETE query:

    DELETE FROM yourTable
WHERE userID = 543678
ORDER BY id
LIMIT 5