what is the difference between these two queries

• jaango123

  Old Hand

  Points: 347

  More actions

  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

• Site Owners

  SSC Guru

  Points: 80332

  More actions

  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.

• Jeff Moden

  SSC Guru

  Points: 1004556

  More actions

  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)
  

• Jonathan AC Roberts

  SSCoach

  Points: 18400

  More actions

  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;

   

   

  • This reply was modified 1 month, 2 weeks ago by  Jonathan AC Roberts. Reason: write a BigQuery