如何基于同表Supplier_Agent列更新Supplier、Agent布尔列？含空值处理

Hey there! Let's break down how to solve this problem—both the general approach and your specific use case.

General Approach #

To update columns to TRUE or FALSE based on values in another column of the same table, you'll use the UPDATE statement combined with a CASE expression (this works across most SQL databases like MySQL, PostgreSQL, SQL Server, etc.). The CASE expression lets you define conditional logic to set values based on the target column's content.

The basic structure looks like this:

UPDATE your_table_name
SET column1 = CASE
    WHEN target_column = 'some_value' THEN TRUE
    WHEN target_column = 'another_value' THEN FALSE
    -- Add more conditions as needed
    ELSE FALSE -- Default value if none of the conditions match
END,
column2 = CASE
    WHEN target_column = 'some_value' THEN FALSE
    WHEN target_column = 'another_value' THEN TRUE
    ELSE FALSE
END;

Your Specific Use Case #

For your scenario where you need to update Supplier and Agent columns based on the Supplier_Agent column (and set both to FALSE if Supplier_Agent has no data), here's a tailored solution.

First, "no data" typically means Supplier_Agent is NULL or an empty string (''). We'll handle both cases. Below are two common scenarios you might encounter based on what Supplier_Agent contains:

Scenario 1: Supplier_Agent indicates a single role (e.g., 'Supplier' or 'Agent') #

If Supplier_Agent holds distinct values like 'Supplier' or 'Agent', use this query:

UPDATE your_table_name
SET Supplier = CASE
    WHEN Supplier_Agent IS NOT NULL AND Supplier_Agent != '' AND Supplier_Agent = 'Supplier' THEN TRUE
    ELSE FALSE
END,
Agent = CASE
    WHEN Supplier_Agent IS NOT NULL AND Supplier_Agent != '' AND Supplier_Agent = 'Agent' THEN TRUE
    ELSE FALSE
END;

Scenario 2: Supplier_Agent can indicate both roles (e.g., 'Supplier,Agent') #

If Supplier_Agent might include multiple roles (like a comma-separated string), adjust the CASE conditions to check for the presence of each role:

UPDATE your_table_name
SET Supplier = CASE
    WHEN Supplier_Agent IS NOT NULL AND Supplier_Agent != '' AND Supplier_Agent LIKE '%Supplier%' THEN TRUE
    ELSE FALSE
END,
Agent = CASE
    WHEN Supplier_Agent IS NOT NULL AND Supplier_Agent != '' AND Supplier_Agent LIKE '%Agent%' THEN TRUE
    ELSE FALSE
END;

Key Tips: #

• Replace your_table_name with the actual name of your table.
• If your database uses 1/0 instead of TRUE/FALSE (like some older MySQL setups), just swap TRUE with 1 and FALSE with 0.
• Always test your logic with a SELECT first to verify changes before running the UPDATE! For example:

  SELECT
      Supplier_Agent,
      CASE
          WHEN Supplier_Agent IS NOT NULL AND Supplier_Agent != '' AND Supplier_Agent = 'Supplier' THEN TRUE
          ELSE FALSE
      END AS Supplier,
      CASE
          WHEN Supplier_Agent IS NOT NULL AND Supplier_Agent != '' AND Supplier_Agent = 'Agent' THEN TRUE
          ELSE FALSE
      END AS Agent
  FROM your_table_name;
  

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