多表联动条件更新：能否通过单一操作实现指定状态变更？

Absolutely! You can use a Common Table Expression (CTE) (supported in most modern databases like PostgreSQL, MySQL 8+, SQL Server) to bundle both updates into one atomic operation. Here's how it works:

Key Assumption #

First, I’m assuming your orders_suppliers table includes a combined_order_id foreign key that links to the primary key of Combined_orders—this is necessary to tie individual supplier orders to their parent combined order.

The Single SQL Statement #

WITH updated_supplier_orders AS (
    -- Step 1: Update the specific supplier order to "accepted" (status 3)
    UPDATE orders_suppliers
    SET order_supplier_status_id = 3
    WHERE 
        -- Add your filter here to target the supplier accepting the order
        -- Example: supplier_id = 123 AND order_id = 456
    RETURNING combined_order_id -- Pass the linked combined order ID to the next step
)
-- Step 2: Update the combined order's status based on all its supplier orders
UPDATE Combined_orders co
SET Combined_order_status_id = 
    CASE
        -- All suppliers have accepted: set status to 3
        WHEN (
            SELECT COUNT(*) 
            FROM orders_suppliers os 
            WHERE os.combined_order_id = co.combined_order_id
        ) = (
            SELECT COUNT(*) 
            FROM orders_suppliers os 
            WHERE os.combined_order_id = co.combined_order_id 
              AND os.order_supplier_status_id = 3
        ) THEN 3
        -- At least one supplier accepted, but not all: set status to 2
        WHEN EXISTS (
            SELECT 1 
            FROM orders_suppliers os 
            WHERE os.combined_order_id = co.combined_order_id 
              AND os.order_supplier_status_id = 3
        ) THEN 2
        -- No suppliers accepted: keep the existing status
        ELSE co.Combined_order_status_id
    END
WHERE co.combined_order_id IN (SELECT combined_order_id FROM updated_supplier_orders);

How This Works #

1. CTE (updated_supplier_orders): This part first updates the specific orders_suppliers record to status 3 (accepted). The RETURNING clause captures the combined_order_id of the parent merged order, so we know which combined order needs its status checked.
2. Main Update: Using the captured combined_order_ids, we check two conditions for each linked combined order:
   • If every supplier order under it is marked as accepted (status 3), we set the combined order status to 3.
   • If at least one is accepted but not all, we set it to 2.
   • If no suppliers have accepted, we leave the status unchanged.

Notes for Different Databases #

• If you’re using MySQL, the syntax for RETURNING in CTE updates is a bit different (you might need to use a temporary table or UPDATE ... JOIN instead), but the core logic stays the same.
• This operation is atomic—either both updates succeed, or neither does, which prevents inconsistent states between the two tables.

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