保留前100行删除其余数据及含2018日期行SQL语法错误咨询

First, let's tackle the syntax error you're seeing: Syntax error near offset 100. The problem here is that most SQL databases don't support using OFFSET directly in a DELETE statement like you would in a SELECT. Instead, you need to use alternative approaches to target the rows you want to remove, depending on your database system.

Let's walk through both of your requirements with corrected, valid SQL:

Requirement 1: Delete all rows except the first 100 #

Important note: "First 100 rows" is ambiguous unless you define an order (e.g., by primary key, date, etc.). Without an ORDER BY, the database will return rows in an undefined order, so your "first 100" might not be consistent.

For MySQL/MariaDB: #

Use a subquery to get the unique identifiers of the first 100 rows, then delete everything not in that set:

DELETE FROM archive
WHERE id NOT IN (
    SELECT id FROM archive
    ORDER BY id -- Replace with your desired sort column (e.g., date)
    LIMIT 100
);

Or a join-based approach (often more efficient for large datasets):

DELETE a
FROM archive a
LEFT JOIN (
    SELECT id FROM archive
    ORDER BY id
    LIMIT 100
) b ON a.id = b.id
WHERE b.id IS NULL;

For PostgreSQL: #

PostgreSQL supports subquery-based filtering or window functions for this task:

DELETE FROM archive
WHERE id NOT IN (
    SELECT id FROM archive
    ORDER BY id
    LIMIT 100
);

Or using ROW_NUMBER() for clearer logic:

WITH ranked_rows AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM archive
)
DELETE FROM archive
WHERE id IN (SELECT id FROM ranked_rows WHERE row_num > 100);

Requirement 2: Delete all rows where date contains '2018', but keep the first 100 of those rows #

Again, define an order for the "first 100" matching rows to ensure consistency. Here's how to implement this correctly:

For MySQL/MariaDB: #

DELETE FROM archive
WHERE date LIKE '2018%'
AND id NOT IN (
    SELECT id FROM archive
    WHERE date LIKE '2018%'
    ORDER BY id -- Replace with your preferred sort column
    LIMIT 100
);

For PostgreSQL: #

Using a window function makes this logic easy to read:

WITH ranked_2018_rows AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM archive
    WHERE date LIKE '2018%'
)
DELETE FROM archive
WHERE id IN (SELECT id FROM ranked_2018_rows WHERE row_num > 100);

Why your original query failed: #

OFFSET is designed for paginating results in SELECT statements. When you try to use it directly in DELETE, the database doesn't recognize the syntax because DELETE expects a condition to identify which rows to remove, not a pagination clause. By using subqueries or window functions, we first isolate the rows we want to keep, then delete everything outside that set.

内容的提问来源于stack exchange，提问作者qadenza