CODEWITHSUNDEEP
mysqlsql
DaveJol2
DaveJol2

Reputation: 87

delete row with lower value only if there are more than n rows

I want to delete the row with lower value only if there are more than 5 rows in the table.

In this example I want to delete the row with id = 4 (value is lower and there are more than 5 rows):

|--------------------|
|   id   |   value   |
|--------------------|
|    1         20    |
|--------------------|
|    2         15    |
|--------------------|
|    3         30    |
|--------------------|
|    4         5     |
|--------------------|
|    5         50    |
|--------------------|
|    6         10    |
|--------------------|

I wonder if it is possible with one query.

Upvotes: 1

Views: 193

Answers (1)

juergen d
juergen d

Reputation: 204756

You can use a left join of the same table

delete t 
from your_table t
left join 
(
  select id
  from your_table
  order by value desc
  limit 5
) tmp on t.id = tmp.id
where tmp.id is null

Upvotes: 2

Related Questions