Post reply

what is the difference between these two queries

  • February 6, 2025 at 7:54 pm

    #4531539

    Please I am struggling to understand the difference between these two queries. It behaves differently as output

    google bigquery query 1

    UPDATE dde-demo-d001.sap_crm.document_flow_root_bods
        SET case_guid = ICT.case_guid
        FROM dde-demo-d001.sap_crm.document_flow_root_bods DFR
        INNER JOIN dde-demo-d001.sap_crm.inferred_case_transactions ICT
        ON DFR.transaction_header_guid = ICT.transaction_header_guid
        WHERE DFR.case_guid IS NULL;

    query 2

    UPDATE dde-demo-d001.sap_crm.document_flow_root_bods DFR
        SET case_guid = ICT.case_guid
        FROM (SELECT transaction_header_guid,case_guid FROM dde-demo-d001.sap_crm.inferred_case_transactions) ICT
        WHERE (DFR.case_guid IS NULL) and (DFR.transaction_header_guid = ICT.transaction_header_guid);

    Context : First query uses inner join and second doesnt use any joins. I cant seem to find any difference between two queries as far as logic ic concerned.

    the query with inner join gives the below error

    UPDATE/MERGE must match at most one source row for each target row

     

    where as the query 2 updates success with 5 rows.

  • February 7, 2025 at 9:07 am

    This was removed by the editor as SPAM

  • February 7, 2025 at 8:10 pm

    #4532474

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • February 9, 2025 at 10:01 pm

    #4533736

    The "JOIN" in the second query is in the WHERE clause as...

    and (DFR.transaction_header_guid = ICT.transaction_header_guid)

    It's also referred to as an "old style" or "Non-SANSI" "Equi-Join"

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 10, 2025 at 1:12 am

    #4533810

    It looks like there is more than one row in ICT with the same transaction_header_guid.

    In SQL Server you could correctly write it as this:

    UPDATE DFR
       SET DFR.case_guid = ICT.case_guid
      FROM dde_demo_d001.sap_crm.document_flow_root_bods AS DFR
     CROSS APPLY (SELECT TOP(1) ICT.case_guid 
                    FROM dde_demo_d001.sap_crm.inferred_case_transactions AS ICT 
                   WHERE DFR.transaction_header_guid = ICT.transaction_header_guid
                   ORDER BY ICT.case_guid -- Ensure deterministic results if necessary
                 ) AS ICT
     WHERE DFR.case_guid IS NULL;

    In BigQuery like this:

    UPDATE dde_demo_d001.sap_crm.document_flow_root_bods AS DFR
       SET DFR.case_guid = sub.case_guid
      FROM (
            -- Get exactly one "best" row per transaction_header_guid
            SELECT transaction_header_guid, case_guid
              FROM (SELECT transaction_header_guid,
                           case_guid,
                           ROW_NUMBER() OVER (PARTITION BY transaction_header_guid ORDER BY case_guid) AS rn
                      FROM dde_demo_d001.sap_crm.inferred_case_transactions
                   )
             WHERE rn = 1
           ) AS sub
     WHERE DFR.case_guid IS NULL
       AND DFR.transaction_header_guid = sub.transaction_header_guid;

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply