基于同表数据更新PREVACCEPTID字段的技术问询

Got it, let's walk through how to solve this PREVACCEPTID update task based on your requirements. I'll structure this with practical SQL examples that fit most relational databases (like MySQL, SQL Server, PostgreSQL):

First, we need to get ordered, distinct ACCEPTIDs for each site to figure out which acceptance record comes before another. Using a window function like ROW_NUMBER() is perfect for assigning a sequence to each record per site:

WITH SiteAcceptanceHistory AS (
    SELECT 
        SiteID,
        ACCEPTID,
        ROW_NUMBER() OVER (PARTITION BY SiteID ORDER BY AcceptanceDate) AS AcceptanceSeq
    FROM YourTableName
    WHERE ACCEPTID != '142692' -- Hold off on the special case for now
)
SELECT * FROM SiteAcceptanceHistory;

Note: Replace YourTableName and AcceptanceDate with your actual table name and the field that determines the order of acceptance records (could be a timestamp or sequence number instead of a date).

With the sequence in place, we can self-join this CTE to link each record to its immediate predecessor and update the main table:

WITH SiteAcceptanceHistory AS (
    SELECT 
        SiteID,
        ACCEPTID,
        ROW_NUMBER() OVER (PARTITION BY SiteID ORDER BY AcceptanceDate) AS AcceptanceSeq
    FROM YourTableName
    WHERE ACCEPTID != '142692'
)
UPDATE t
SET t.PREVACCEPTID = prev.ACCEPTID
FROM YourTableName t
JOIN SiteAcceptanceHistory curr ON t.SiteID = curr.SiteID AND t.ACCEPTID = curr.ACCEPTID
LEFT JOIN SiteAcceptanceHistory prev ON curr.SiteID = prev.SiteID AND curr.AcceptanceSeq = prev.AcceptanceSeq + 1
WHERE t.ACCEPTID != '142692';

This sets PREVACCEPTID to the immediately preceding ACCEPTID for the same site. If it's the first acceptance record for the site, PREVACCEPTID will stay NULL (or whatever default you have set).

For records where ACCEPTID is '142692', you mentioned needing to validate against a child table. Let's assume your child table (let's call it ChildTable) links to the main table via ACCEPTID, and you need to check for valid related records before setting PREVACCEPTID.

First, run this query to verify the validation and get the correct previous ACCEPTID:

SELECT 
    t.SiteID,
    t.ACCEPTID,
    CASE WHEN EXISTS (
        SELECT 1 FROM ChildTable ct 
        WHERE ct.ACCEPTID = t.ACCEPTID 
        -- Add your specific validation rules here, e.g., ct.Status = 'Approved'
    ) THEN (
        -- Get the most recent non-special ACCEPTID for the site if validation passes
        SELECT TOP 1 ACCEPTID 
        FROM YourTableName 
        WHERE SiteID = t.SiteID AND ACCEPTID != '142692' 
        ORDER BY AcceptanceDate DESC
    ) ELSE NULL END AS ValidPrevAcceptID
FROM YourTableName t
WHERE t.ACCEPTID = '142692';

Tweak the EXISTS clause to match your actual validation logic for the child table. The subquery pulls the latest valid historical ACCEPTID for the site when validation passes.

Then, use this to update the special entries:

WITH SpecialCaseValidation AS (
    SELECT 
        t.SiteID,
        t.ACCEPTID,
        CASE WHEN EXISTS (
            SELECT 1 FROM ChildTable ct 
            WHERE ct.ACCEPTID = t.ACCEPTID 
            -- Add your validation conditions here
        ) THEN (
            SELECT TOP 1 ACCEPTID 
            FROM YourTableName 
            WHERE SiteID = t.SiteID AND ACCEPTID != '142692' 
            ORDER BY AcceptanceDate DESC
        ) ELSE NULL END AS ValidPrevAcceptID
    FROM YourTableName t
    WHERE t.ACCEPTID = '142692'
)
UPDATE t
SET t.PREVACCEPTID = sc.ValidPrevAcceptID
FROM YourTableName t
JOIN SpecialCaseValidation sc ON t.SiteID = sc.SiteID AND t.ACCEPTID = sc.ACCEPTID;

• Always test first: Run the SELECT versions of these queries before executing the UPDATE to make sure you're getting the right values (no one wants accidental data changes!).
• Adjust for your DB: If your database doesn't support CTEs (like older MySQL versions), rewrite these using subqueries or temporary tables.
• Order matters: Double-check that the ORDER BY clause matches how you define "historical" (e.g., use a timestamp instead of date if precision matters).

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