Syntax for UPDATE with LEFT JOIN

Question

How do you insert this query with an update? I've tried all possible combinations and nothing seems to work!

UPDATE Test_table2
SET Pledge_Status = closed
WHERE (
    SELECT SUM(pledgers.Pledge_payment_amt) AS pledged
    FROM Test_table2  AS recipients
    LEFT JOIN Test_table2 AS pledgers
    ON recipients.GIFT_NO = pledgers.PLEDGE_GIFT_NO
    GROUP BY recipients.GIFT_NO
    HAVING recipients.Pledge_Amt >= pledged
    ORDER BY recipients.CRSID ASC
);

Schema (all varchar):

ID, Name, Pledge_Amount , Pledge_payment_amt , Gift_No, Pledge_Gift_No, Pledge_Open/Closed

Thank you so very much!

Answer 1

Parallel to this question I suggest that you use WHERE ... IN SELECT. Also, you need to pass the ID of each row from the subselect to the main query, and since you didn't provide a table schema, we can only guess here:

UPDATE Test_table2
SET Pledge_Status = closed
WHERE [your id column] IN (
    SELECT [your id column]
    FROM Test_table2  AS recipients
    LEFT JOIN Test_table2 AS pledgers
    ON recipients.GIFT_NO = pledgers.PLEDGE_GIFT_NO
    GROUP BY recipients.GIFT_NO
    HAVING recipients.Pledge_Amt >= SUM(pledgers.Pledge_payment_amt)
    /*ORDER BY recipients.CRSID ASC <-- no need to order anything here */
);