MySQL UPDATE update data method

Basic syntax of UPDATE

UPDATEStatements are used to modify data in an existing table. It usually hasSETUse together to specify the field to update and its new value. You can also useWHEREclause to limit records to be updated.

Basic syntax:

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

table_name: The name of the table to be updated.
column1, column2: The field name to be updated.
value1, value2: The updated new value.
condition: Conditions that limit the update scope. If notWHEREclause, updates all records in the table.

Update single record

To update a single record, you need to useWHEREclause to ensure that only records that meet the criteria are updated.

Example: Update the email address of the user with username ‘alice’

UPDATE users
SET email = '@'
WHERE username = 'alice';

This statement willusersIn the tableusernameFor users of ‘alice’emailUpdated to ‘@’.

Update multiple records

You can update multiple records with the right conditions.

Example: AllstatusFor users of ‘inactive’statusUpdated to ‘active’

UPDATE users
SET status = 'active'
WHERE status = 'inactive';

This statement willusersAll in the tablestatusThe record for ‘inactive’ is updated to ‘active’.

Restrict update records using WHERE

To avoid updating all records, you can useWHEREclauses to limit the update scope. NoWHEREWhen a clause, all records in the table are updated.

Example: Updated all users older than 30 have the status of ‘senior’

UPDATE users
SET status = 'senior'
WHERE age > 30;

This statement is updated onlyageUsers greater than 30.

Update multiple fields using SET

You can be in the sameUPDATETo update multiple fields in a statement, just use commas to separate the assignments of each field.

Example: Update the user'semailandstatus

UPDATE users
SET email = '@', status = 'active'
WHERE username = 'bob';

This statement willusernameFor users of ‘bob’emailUpdated to ‘@‘, andstatusUpdated to ‘active’.

Update with subqueries

existUPDATEIn a statement, subqueries can be used to calculate updated values dynamically.

Example: Update the order status in the orders table to ‘shipped’ and change itshipped_dateSet as current date

UPDATE orders
SET order_status = 'shipped', shipped_date = (SELECT CURRENT_DATE())
WHERE order_status = 'processing';

This statement willorder_statusThe status of all orders for ‘processing’ is updated to ‘shipped’ and theshipped_dateSet to the current date.

UPDATE Use JOIN

You can also useJOINclause to update the data in the table.

Typically, this is used to update records based on the values of another table.

Example: Update the user_email field in the orders table based on the email in the users table

UPDATE orders o
JOIN users u ON o.user_id = 
SET o.user_email = 
WHERE o.order_status = 'pending';

This statement willordersAll in the tableorder_statusRecorded for ‘pending’user_emailUpdated to correspondingusersThe tableemail。

Use LIMIT to limit the number of updates

By usingLIMITclause, you can limit the number of updated items.

In some cases, you may want to update only the first few records in the table.

Example: Update status of the first 5 records in the users table

UPDATE users
SET status = 'inactive'
LIMIT 5;

This statement willusersThe first 5 records in the tablestatusUpdated to ‘inactive’.

References:

MySQL UPDATE official documentation
MySQL JOIN official documentation

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.