Updating Data ​

To update records in a database table, the UPDATE statement is used.

Basic Syntax ​

The basic syntax for the UPDATE statement is:

UPDATE <table_name> SET column1 = value1, column2 = value2, ... WHERE ...;

Example ​

To update the students table, changing the name and score for the record with id = 1:

-- Update record with id = 1:
UPDATE students SET name = '大牛', score = 66 WHERE id = 1;
-- Check the result:
SELECT * FROM students WHERE id = 1;

Updating Multiple Records ​

You can update multiple records at once by using a condition that matches several rows:

-- Update records with id = 5, 6, 7:
UPDATE students SET name = '小牛', score = 77 WHERE id >= 5 AND id <= 7;
-- Check the result:
SELECT * FROM students;

Using Expressions ​

You can also use expressions to modify fields. For example, to increase the score of all students scoring below 80 by 10 points:

-- Update scores for students with score < 80:
UPDATE students SET score = score + 10 WHERE score < 80;
-- Check the result:
SELECT * FROM students;

No Matching Records ​

If the WHERE clause does not match any records, the UPDATE statement will not throw an error and will not update any records:

-- Attempt to update record with id = 999:
UPDATE students SET score = 100 WHERE id = 999;
-- Check the result:
SELECT * FROM students;

Caution with WHERE Clause ​

Be cautious, as an UPDATE statement can be executed without a WHERE clause, which would update all records in the table:

UPDATE students SET score = 60;

It's best to run a SELECT statement first to ensure the WHERE clause is targeting the intended records.

MySQL Behavior ​

In MySQL, the UPDATE statement returns the number of affected rows and the number of rows that matched the WHERE condition. For example:

mysql> UPDATE students SET name = '大宝' WHERE id = 1;
Query OK, 1 row affected

Summary ​

Using UPDATE, you can modify one or more records in a table efficiently, but always verify the WHERE conditions to prevent unintended updates.