将Selling记录的close_units值迁移至Agent to Agent Ref记录的技术求助

Got it, let's tackle this data correction task. Based on your sample records, we need two core actions:

1. Copy the close_units value from the Selling record to the matching Agent to Agent Ref record (identified by the same sale_no, referring_agentID matching the Selling's payeeID, and ref_agent_type = 'Selling')
2. Reset the original Selling record's close_units to 0

Here's how to do this with SQL (adjust your_table to your actual table name):

Step 1: Update the Agent to Agent Ref Record #

First, we'll use a self-join to link the Selling record to its corresponding Agent to Agent Ref entry, then transfer the close_units value:

UPDATE your_table agent_ref
JOIN your_table selling_rec
  ON agent_ref.sale_no = selling_rec.sale_no
  AND agent_ref.ref_agent_type = 'Selling'
  AND agent_ref.referring_agentID = selling_rec.payeeID
  AND selling_rec.record_type = 'Selling'
SET agent_ref.close_units = selling_rec.close_units
WHERE agent_ref.ComType = 'Agent to Agent Ref'
  AND agent_ref.record_type = 'Agent Paid Ref';

Step 2: Reset the Selling Record's close_units #

Next, we'll zero out the close_units for Selling records that have a matching Agent to Agent Ref entry:

UPDATE your_table selling_rec
SET close_units = 0
WHERE selling_rec.record_type = 'Selling'
AND EXISTS (
    SELECT 1
    FROM your_table agent_ref
    WHERE agent_ref.sale_no = selling_rec.sale_no
      AND agent_ref.ComType = 'Agent to Agent Ref'
      AND agent_ref.record_type = 'Agent Paid Ref'
      AND agent_ref.ref_agent_type = 'Selling'
      AND agent_ref.referring_agentID = selling_rec.payeeID
);

Optional: Wrap in a Transaction #

To ensure both operations succeed or fail together (avoiding partial data changes), wrap them in a transaction:

BEGIN TRANSACTION;

-- Update Agent to Agent Ref
UPDATE your_table agent_ref
JOIN your_table selling_rec
  ON agent_ref.sale_no = selling_rec.sale_no
  AND agent_ref.ref_agent_type = 'Selling'
  AND agent_ref.referring_agentID = selling_rec.payeeID
  AND selling_rec.record_type = 'Selling'
SET agent_ref.close_units = selling_rec.close_units
WHERE agent_ref.ComType = 'Agent to Agent Ref'
  AND agent_ref.record_type = 'Agent Paid Ref';

-- Reset Selling record
UPDATE your_table selling_rec
SET close_units = 0
WHERE selling_rec.record_type = 'Selling'
AND EXISTS (
    SELECT 1
    FROM your_table agent_ref
    WHERE agent_ref.sale_no = selling_rec.sale_no
      AND agent_ref.ComType = 'Agent to Agent Ref'
      AND agent_ref.record_type = 'Agent Paid Ref'
      AND agent_ref.ref_agent_type = 'Selling'
      AND agent_ref.referring_agentID = selling_rec.payeeID
);

COMMIT;

Key Notes: #

• Replace your_table with the actual name of your database table
• Test these queries on a staging copy of your data first to verify they work as expected
• The EXISTS clause in the second update ensures we only reset Selling records that have a corresponding Agent to Agent Ref entry (so we don't accidentally zero out Selling records that don't need this change)

内容的提问来源于stack exchange，提问作者Doug