I am trying to optimize DELETE query where I am searching almost 200-500 values in one go like below:
DELETE FROM trade WHERE (tradeId, sourceFlag) IN (('1321', true), ('2321321', false) );
I want to improve like below query but want to change single id to array of ids.
DELETE
FROM
trade AS t
WHERE
EXISTS(
WITH
toDelete AS (
SELECT
tradeId,
sourceFlag
FROM
UNNEST([STRUCT('A3123123D' AS tradeId, TRUE AS sourceFlag),
STRUCT('A2323222D' AS tradeId, FALSE AS sourceFlag)]) )
SELECT
1
FROM
toDelete AS td
WHERE
td.tradeId = t.tradeId
AND t.sourceFlag = td.sourceFlag)
I want to replace tradeId inside struct as array of different ids instead of string. So you can see that some ids needs to be check with sourceflag as true and some with false.
Can anyone help me on this?
Update: I created below query this actually help me to solve problem but not completely.
SELECT tradeId, sourceFlag
FROM
trade AS t
WHERE
EXISTS(
WITH
toDelete_parent AS (
WITH
toDelete AS (
SELECT
['WD29256D','WS29226D','WA29256D'] AS tradeId, TRUE AS sourceFlag,
UNION ALL SELECT ['1232270002067','1232270008366','C0X700000001','SW2308151242594','1232230007933','SW2308151255491','SW230815122721','TT23081515314','TT230815153424','TT2308161324260','SW2308151454114','1232230007364'] AS tradeId, FALSE AS sourceFlag)
SELECT flattened_tradeId, sourceFlag
FROM toDelete
CROSS JOIN UNNEST(toDelete.tradeId) AS flattened_tradeId)
SELECT
1
FROM
toDelete_parent AS td
WHERE
td.flattened_tradeId = t.tradeId
AND t.sourceFlag = td.sourceFlag)