Detailed explanation of the UPDATE statement in MySQL

UPDATE statement in MySQL

UPDATEStatement forReviseData in the table can be updated with single or multiple rows of data.

1. UPDATE syntax

UPDATE Table name
SET List1 = value1, List2 = value2, ...
WHERE condition;

⚠ Attention：

• Must addWHERE, otherwise all rows will be modified.
• SETAfter that, multiple fields can be updated at the same time.
• WHEREUsed for filteringRecords that need to be updated。

2. Basic UPDATE

Example

(1) Update a single field

UPDATE users
SET age = 30
WHERE id = 1;

explain：

Willid = 1ofageUpdated to30。

(2) Update multiple fields

UPDATE users
SET age = 30, city = 'Shanghai'
WHERE id = 1;

explain：

id = 1ofageChange to30，cityChange to'Shanghai'。

(3) Update all rows (use with caution!)

UPDATE users SET status = 'inactive';

⚠ Danger：

• NoWHERE, the entire table will be updated!
• statusAll data in the field becomes'inactive'。

How to prevent misoperation?

Use firstSELECTCheck the update scope

：

SELECT * FROM users WHERE status = 'active';

Enable transaction (if supported)

：

START TRANSACTION;
UPDATE users SET status = 'inactive';
ROLLBACK;  -- Cancel
COMMIT;    -- Confirm update

3. UPDATE combined with WHERE conditions

(1) Use=Exact match

UPDATE users SET age = 25 WHERE name = 'Alice';

renewname = 'Alice'User'sagefor25。

(2) UseANDandOR

UPDATE users
SET status = 'inactive'
WHERE age > 30 AND city = 'Beijing';

renewage > 30 and city = 'Beijing'user.

UPDATE users
SET status = 'inactive'
WHERE age > 50 OR city = 'Shanghai';

renewage > 50orcity = 'Shanghai'user.

(3) UseIN

UPDATE users
SET vip_status = 'gold'
WHERE id IN (1, 2, 3, 5);

renew idexist(1,2,3,5)users in.

(4) UseBETWEEN

UPDATE users
SET level = 'senior'
WHERE age BETWEEN 30 AND 40;

renew ageexist30-40between users.

(5) UseLIKE

UPDATE users
SET department = 'Tech'
WHERE email LIKE '%@';

Update allemailby@The ending user.

4. UPDATE combined with JOIN

Batch update data,passJOINRelated two tables:

UPDATE users u
JOIN orders o ON  = o.user_id
SET u.vip_status = 'gold'
WHERE o.total_amount > 1000;

explain：

• usersTable andordersTable passeduser_idRelated.
• When the user'stotal_amount > 1000When updatedusers.vip_statusfor'gold'。

5. UPDATE combined with CASE (condition update)

UPDATE users
SET vip_status = 
    CASE 
        WHEN age > 50 THEN 'platinum'
        WHEN age BETWEEN 30 AND 50 THEN 'gold'
        ELSE 'silver'
    END;

explain：

• age > 50 → platinum
• age at 30-50Between →gold
• Other situations →silver

6. UPDATE combined with LIMIT

If you only want to modify it at one timePart of the data：

UPDATE users SET status = 'inactive' ORDER BY id ASC LIMIT 10;

explain：

• Update onlyFirst 10 linesData (pressidsorted ascending order).
• Suitable for large table batch updates。

7. UPDATE combined with ORDER BY

When updating, pressSpecific orderProcessing data:

UPDATE users SET rank = rank + 1 ORDER BY age DESC;

explain：

according toageUpdated from large to smallrank。

8. UPDATE Batch Replace Fields

(1) Replace part of the string

UPDATE users
SET email = REPLACE(email, '', '')
WHERE email LIKE '%';

explain：

WillemailInsideReplace with。

(2) Splice strings

UPDATE users
SET username = CONCAT(username, '_new');

explain：

existusernameAdd at the end_new。

9. UPDATE transaction control

ifUPDATEMay affect multiple rows of data, it is recommendedUsage transactions：

START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;
COMMIT;

If it fails, rollback：

ROLLBACK;

10. Prevent UPDATE from affecting the entire table

To prevent forgettingWHEREStatement, can be openedsafe updates：

SET SQL_SAFE_UPDATES = 1;

If it is not added during updateWHERE, will report an error:

ERROR 1175 (HY000): You are using safe update mode...

closure(Only if necessary):

SET SQL_SAFE_UPDATES = 0;

Summarize

🚀 Focus：

• Must addWHERE, avoid mistakenly updating all data!
• When batch update of large tables, update them in batches (LIMIT) to avoid locking tables.
• useCASEConduct conditions updates to improve flexibility.
• When multiple tables are involved, useJOINUpdate data.
• Before large-scale updates,SELECTPreview results.

In this way, yourUPDATEStatements can be fast and safe! 💡

This is all about this article about the UPDATE statement in MySQL. For more related contents of mysql update statements, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!