db2 - DELETE 语句部分删除数据

Question

当我在 DB2 中执行以下查询时，它不会立即删除数据。

我有 800 条记录，其中每 2 条记录是重复的，我想删除 2 条记录中的 1 条记录，这样它将在 DB 中留下 400 条记录。

以下是 RESERVATION_NUMBER 的示例。

DELETE 
FROM   reservation_number 
WHERE  reservation_id IN (SELECT reservation_id 
                          FROM   (SELECT ROW_NUMBER() 
                                           OVER() AS RN, 
                                         msr1.reservation_number, 
                                         msr1.reservation_id, 
                                         msr1.used_flag 
                                  FROM   reservation_number msr1, 
                                         reservation_number msr2 
                                  WHERE 
              msr1.reservation_number = msr2.reservation_number 
              AND msr1.reservation_id <> msr2.reservation_id 
                                  ORDER  BY msr1.reservation_number) 
                          WHERE  Mod (rn, 2) = 0 
                          ORDER  BY reservation_number) 

如果我多次执行此查询，它将删除完整的数据。正在以下列方式删除数据 -

400、168、076、038、019、003、001

标签： db2sql-delete

Answer 1

我得到了修复。我在 OVER() 中缺少一个参数。

这是正确的查询

DELETE
FROM
    RESERVATION_NUMBER
WHERE
    RESERVATION_ID IN (
    SELECT
        RESERVATION_ID
    FROM
        (
        SELECT
            ROW_NUMBER() OVER(ORDER BY msr1.RESERVATION_NUMBER) AS RN,
            msr1.RESERVATION_NUMBER,
            msr1.RESERVATION_ID,
            msr1.USED_FLAG
        FROM
            RESERVATION_NUMBER msr1 ,
            RESERVATION_NUMBER msr2
        WHERE
            msr1.RESERVATION_NUMBER = msr2.RESERVATION_NUMBER
            AND msr1.RESERVATION_ID <> msr2.RESERVATION_ID
        ORDER BY
            msr1.RESERVATION_NUMBER )
    WHERE
        MOD (RN,2)=1 
    ORDER BY
        RESERVATION_NUMBER )