Need help with Delete query

SQL++
,
1

Hi I need delete query which deletes records for the past 10 days of the given date.

if given date is x , i need to delete records which fall between x-10 and x

x is in epochdate by the way.

The given date , x , will come from the result of another query.

Example:

step 1: select deleteDate from Y limit 1 [Limit 1 is there because any record is fine]
I need to use this result in the below query
step 2: Delete from T where T.date between deleteDate-10 and deleteDate

Final query: I can write like this

Delete from T where T.date between (select deleteDate from Y limit 1)-10 and (select deleteDate from Y limit 1)

However I want this query to be simplified or refactored to something short

I wanted to use “With” clause like below , But i found we cant use “WITH” with Delete

WITH delDate as (select deleteDate from x limit 1)
Delete from t where t.date between delDate-10 and delDate

Above doesnt work as With with DELETE is not possible.

What would be the simpler way to write this query ?

Thanks for reading.

2

You can use:

DELETE FROM default.s1.d1
USE KEYS (
    SELECT RAW meta().id
    FROM default.s1.d1 sd1
    LET delDate = (SELECT RAW del_date FROM default.s1.d2 LIMIT 1)[0]
    WHERE sd1.deleteDate BETWEEN delDate - 10 AND delDate
);

This of course deletes 11 days worth as BETWEEN is inclusive.

HTH.

3 
MERGE INTO default.s1.d1 AS m USING (SELECT del_date FROM default.s1.d2 LIMIT 1) AS s
ON m.date BETWEEN s.del_date - 10 AND s.del_date
WHEN MATCHED THEN DELETE ;

Due to merge m may not use covered index.

For covering index you can use @dh suggestion with little variation to take advantage of indexscan push down described Subquery handling and potential query rewrite of IN List Handling Improvements in Couchbase Server 6.5 - The Couchbase Blog i.e. subquery results can’t be used in current indexscan, If those coming from CTE it can use because those are evaluated before indexscan .

DELETE FROM default.s1.d1
USE KEYS (
    WITH delDate AS ((SELECT RAW del_date FROM default.s1.d2 LIMIT 1)[0])
    SELECT RAW meta().id
    FROM default.s1.d1 sd1
    WHERE sd1.deleteDate BETWEEN delDate - 10 AND delDate
);