如何一次性为Employee表新增的Gender列批量填充指定数据？

There are a couple of clean, efficient ways to set the gender values for your four employees in a single SQL statement, skipping the hassle of running individual UPDATE commands for each row.

Method 1: Using a CASE Statement #

This is the simplest approach for a small set of rows. You can directly map each employee Id to its corresponding gender value in the SET clause:

UPDATE employee
SET gender = CASE Id
    WHEN 1 THEN 'M'
    WHEN 2 THEN 'M'
    WHEN 3 THEN 'M'
    WHEN 4 THEN 'F'
END
WHERE Id IN (1, 2, 3, 4); -- Optional but recommended to limit updates to target rows

Why this works: #

• The CASE expression checks each row's Id and returns the matching gender value.
• The WHERE clause ensures you only modify the four rows you care about—this is a safe guard to avoid accidentally updating other employees if more are added to the table later.

Method 2: Using a JOIN with a Derived Table #

If you expect to update more rows down the line, this scalable approach lets you define all your ID-gender pairs in one easy-to-edit block:

UPDATE e
SET e.gender = g.target_gender
FROM employee e
INNER JOIN (
    VALUES
        (1, 'M'),
        (2, 'M'),
        (3, 'M'),
        (4, 'F')
) AS g(target_id, target_gender) 
ON e.Id = g.target_id;

Why this works: #

• The derived table g acts as a temporary lookup table of employee IDs and their desired gender values.
• The INNER JOIN links this lookup table to your employee table, and the UPDATE applies the correct gender to each matching row.
• Adding more entries is as simple as appending new rows to the VALUES list, making this perfect for larger batch updates.

Both methods will set the gender values exactly as you need (M, M, M, F for IDs 1-4) in one execution. Pick the one that fits your current and future needs best!

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