CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
user3461502
user3461502

Reputation: 323

Need to update several different columns in one table from only one column found in another table

I have two tables, TBL_A an TBL_B.

TBL_A looks like this:

enter image description here

TBL_B looks like this:

enter image description here

I need to update TBL_B based on the information found in TBL_A for DATETIME that corresponds to a specific status, so:

 when status = 'CLAIMED' put DATETIME in CLAIMED_DATETIME
 when status = 'BOUGHT' put DATETIME in BOUGHT_DATETIME
 when status = 'RETURNED' put DATETIME in RETURNED_DATETIME

I have this query:

   MERGE INTO TBL_B B
USING (
      SELECT * FROM TBL_A
    PIVOT(MIN(ACCOUNT_REWARD_UPDATED_AT_DATETIME) FOR ACCOUNT_REWARD_STATUS IN ('claimed', 'bought', 'returned')) 
     AS P (number_id, country, amount, currency, file_tp, ingestion_time, claimed, bought, returned)
) A ON (
       A.number_id = B.number_id, 
    and  A.country = B.country, 
    and A.amount = B.amount, 
    and A.currency = B.currency, 
    and A.file_tp = B.file_tp 
    and A.ingestion_time = B.ingestion_time
 )
WHEN MATCHED THEN
  UPDATE SET
    B.CLAIMED_DATETIME = IFF(A.claimed IS NOT NULL, A.claimable , '2999-12-31 23:59:5'),
    B.BOUGHT_DATETIME = IFF(A.bought IS NOT NULL, A.claimed , '2999-12-31 23:59:5'),
    B.RETURNED_DATETIME = IFF(A.reverted IS NOT NULL, A.reverted, '2999-12-31 23:59:5');

When I join on ALL columns, file_tp and ingestion_time are different for every status (claimed, bought and returned), and the rest of the columns are the same for each of the status. When I run the query above, it only updates 1 DATE, not all of them. I realized it's because I can't join with the FILE_TP and INGESTION_TIME since these are different for each status. HOWEVER, when I remove these from the join condition, I get an error:

   MERGE INTO TBL_B B
USING (
      SELECT * FROM TBL_A
    PIVOT(MIN(DATETIME) FOR STATUS IN ('claimed', 'bought', 'returned')) 
     AS P (number_id, country, amount, currency, file_tp, ingestion_time, claimed, bought, returned)
) A ON (
       A.number_id = B.number_id, 
    and  A.country = B.country, 
    and A.amount = B.amount, 
    and A.currency = B.currency, 
    --and A.file_tp = B.file_tp 
    --and A.ingestion_time = B.ingestion_time
 )
WHEN MATCHED THEN
  UPDATE SET
    B.CLAIMED_DATETIME = IFF(A.claimed IS NOT NULL, A.claimable , '2999-12-31 23:59:5'),
    B.BOUGHT_DATETIME = IFF(A.bought IS NOT NULL, A.claimed , '2999-12-31 23:59:5'),
    B.RETURNED_DATETIME = IFF(A.reverted IS NOT NULL, A.reverted, '2999-12-31 23:59:5');

ERROR:

Duplicate row detected during DML action Row Values:

Does anyone know how I can fix this?

Upvotes: 1

Views: 360

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

I suspect the pivot is not doing what you expect it to do - inspect its results.

Try changing this:

      SELECT * FROM TBL_A
    PIVOT(MIN(DATETIME) FOR STATUS IN ('claimed', 'bought', 'returned')) 
     AS P (number_id, country, amount, currency, file_tp, ingestion_time, claimed, bought, returned)
) A ON (
       A.number_id = B.number_id, 
    and  A.country = B.country, 
    and A.amount = B.amount, 
    and A.currency = B.currency, 
    --and A.file_tp = B.file_tp 
    --and A.ingestion_time = B.ingestion_time

to this:

    SELECT * 
    FROM (select number_id, country, amount, currency from TBL_A)
    PIVOT(MIN(DATETIME) FOR STATUS IN ('claimed', 'bought', 'returned')) 
    AS P (number_id, country, amount, currency, claimed, bought, returned)
) A ON (
    A.number_id = B.number_id, 
    and A.country = B.country, 
    and A.amount = B.amount, 
    and A.currency = B.currency,

Upvotes: 1

Related Questions