How can we update multiple rows in the same column with different values in bulk?

2 years ago
Noah Thompson
1 minute

To update different values in the same column in bulk, you can use the UPDATE statement combined with the CASE statement. Here is an example:

UPDATE 表名
SET 列名 = 
    CASE 
        WHEN 条件1 THEN 值1
        WHEN 条件2 THEN 值2
        ...
        ELSE 默认值
    END
WHERE 条件;

By using a CASE statement in the SET clause, you can set different values based on different conditions. You can add filtering conditions in the WHERE clause to determine which rows to update.

For example, let’s say there is a table named “students” with a column “score” that needs to be updated in bulk. We want to set students who score 80 or above to “A”, students who score between 60 and 79 to “B”, and the rest of the students to “C”. The following code can be used to update this:

UPDATE students
SET score = 
    CASE 
        WHEN score >= 80 THEN 'A'
        WHEN score >= 60 THEN 'B'
        ELSE 'C'
    END;

Please modify the table names, column names, conditions, and values based on the specific table structure and requirements when needed in practice.

bannerAds