删除所有行一定数量后

I have 5000 rows of data. and I want to delete all the rows after 1000 how would I do this?

我有5000行数据。我想删除1000之后的所有行我该怎么做?

Delete from table
where *
limit 1000

is this correct?

它是否正确?

3 个解决方案

#1

SQL Server

WITH cte
     AS (SELECT *,
                row_number() OVER (ORDER BY some_column) AS rn
         FROM   TABLE)
DELETE FROM cte
WHERE  rn > 1000 

#2

DELETE FROM TABLE
WHERE ID NOT IN
(
   SELECT ID FROM TABLE LIMIT 1000
)

This query will :

此查询将:

1 - Select THE FIRST 1000 records from your database

1 - 从数据库中选择THE FIRST 1000条记录

2 - Deletes all the records that are not in the list of the first 100 records

2 - 删除前100条记录列表中不存在的所有记录

#3

SQL Fiddle

Oracle 11g R2 Schema Setup:

Oracle 11g R2架构设置:

CREATE TABLE test ( A ) AS
SELECT LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 5000;

Query 1:

SELECT COUNT(*) FROM test

Results:

| COUNT(*) |
|----------|
|     5000 |

Query 2:

DELETE FROM test
WHERE A IN ( SELECT A FROM (SELECT A, ROWNUM rn FROM test ) WHERE rn > 1000 )

Results:

Query 3:

SELECT COUNT(*) FROM test

Results:

| COUNT(*) |
|----------|
|     1000 |