CTE с DELETE - альтернатива хранилищу данных SQL

Question

Я хотел бы удалить все строки в таблице, где batchId (порядковый номер) старше, чем предыдущие два. Я мог бы сделать это в базе данных SQL с помощью запроса:

WITH CTE AS(
    SELECT
        *,
        DENSE_RANK() OVER(ORDER BY BATCHID DESC) AS RN
    FROM MyTable
)
DELETE FROM CTE WHERE RN>2

Но то же самое не допускается в хранилище данных SQL для этого. Ищете альтернативы здесь.

Answer 1

Вы можете попробовать использовать JOIN

delete d from MyTable d
join 
(
 SELECT
        *,
        RN = ROW_NUMBER() OVER(PARTITION BY BATCH_ID ORDER BY BATCH_ID DESC)
    FROM MyTable
)A on d.batch_id=A.batch_id where RN >2

Answer 2

Хранилище данных SQL Azure поддерживает только ограниченную площадь поверхности T-SQL и CTE для DELETE операции и DELETEs с FROM пункты, которые приведут к следующей ошибке:

Msg 100029, уровень 16, состояние 1, строка 1
Предложение FROM в настоящее время не поддерживается в операторе DELETE.

Тем не менее, он поддерживает подзапросы, поэтому один из способов написать ваше утверждение так:

DELETE dbo.MyTable
WHERE BATCHID Not In ( SELECT TOP 2 BATCHID FROM dbo.MyTable ORDER BY BATCHID DESC );

Этот синтаксис поддерживается в хранилище данных SQL Azure, и я его протестировал. Я не уверен, насколько эффективно это будет на миллиардах строк, хотя. Вы также можете рассмотреть возможность переключения разделов.

Если вы удаляете большую часть таблицы, возможно, имеет смысл использовать CTAS для помещения данных, которые вы хотите сохранить, в новую таблицу, например что-то вроде этого:

-- Keep the most recent two BATCHIDS
CREATE TABLE dbo.MyTable2
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH( BATCHID )
    -- Add partition scheme here if required
)
AS
SELECT  *
FROM dbo.MyTable
WHERE BATCHID In ( SELECT TOP 2 BATCHID FROM dbo.MyTable ORDER BY BATCHID DESC )
OPTION ( LABEL = 'CTAS : Keep top two BATCHIDs' );
GO

-- Rename or DROP old table
RENAME OBJECT dbo.MyTable TO MyTable_Old;
RENAME OBJECT dbo.MyTable2 TO MyTable;
GO

-- Optionally DROP MyTable_Old if everything has been successful
-- DROP TABLE MyTable_Old

Этот метод описан более подробно здесь.

Answer 3

Ты можешь попробовать:

delete t from mytable t
    where batchId < (select max(batchid) from mytable);

О, если вы хотите оставить два, возможно, это сработает:

delete t from mytable t
    where batchId < (select batchid
                     from mytable
                     group by batchid
                     limit 1 offset 1
                    );