-1

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)
Kammy
  • 409
  • 1
  • 7
  • 26

0 Answers0