更新指定行status字段时，将同列其他行status设为NO是否可行？

Absolutely! This specific update behavior is totally doable with a targeted SQL statement that addresses both parts of your request in one go.

Here's What the Solution Covers #

Your needs boil down to two key actions:

• Set the status of the row with id=5 to 'Yes' (with a capitalized 'Y')
• Flip all existing 'yes' values in other rows to 'no', while leaving original 'no' values untouched

The SQL Implementation #

Use a CASE statement inside your UPDATE query to handle each condition dynamically:

UPDATE your_table_name
SET status = CASE
    WHEN id = 5 THEN 'Yes'
    WHEN status = 'yes' THEN 'no'
    ELSE status
END;

How This Matches Your Expected Result #

Let’s map this to your original data to confirm:

• Row 1 (id=1, status='no'): Falls into the ELSE clause, stays 'no'
• Row 2 (id=2, status='yes'): Triggers the second condition, becomes 'no'
• Row 3 (id=3, status='no'): Remains 'no' via the ELSE clause
• Row 4 (id=4, status='yes'): Triggers the second condition, becomes 'no'
• Row 5 (id=5, status='--'): Triggers the first condition, becomes 'Yes'

This produces exactly the outcome you’re looking for.

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