MySQL UPDATE Update

If we need to modify or update data in MySQL, we can use theUPDATEcommand to operate.

Syntax

The following is the general SQL syntax for the UPDATE command to modify data in MySQL tables:

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

Parameter description:

• table_nameis the name of the table where you want to update data.
• column1, column2, ... are the names of the columns you want to update.
• value1, value2, ... are the new values used to replace the old values.
• WHERE conditionis an optional clause used to specify the rows to update. If theWHEREclause is omitted, all rows in the table will be updated.

More notes:

• You can update one or more fields at the same time.
• You can specify any condition in the WHERE clause.
• You can update data in a single table at the same time.

The WHERE clause is very useful when you need to update data for specified rows in a data table.

Examples

The following examples demonstrate how to use the UPDATE statement.

1. Update the value of a single column:

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

2. Update the values of multiple columns:

UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;

3. Update values using expressions:

UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

The above SQL statement increases the price of every product in the 'Electronics' category by 10%.

4. Update all rows that meet the condition:

UPDATE students
SET status = 'Graduated';

The above SQL statement updates the status of all students to 'Graduated'.

5. Update values using a subquery:

UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

The above SQL statement uses a subquery to calculate the total purchase amount for each customer of type 'Premium', and updates that value into the total_purchases column.

Note:When using the UPDATE statement, make sure you provide sufficient conditions to ensure that only the rows you want to update are modified. If you do not provide a WHERE clause, all rows in the table will be updated, which may lead to unpredictable results.

---

Update Data via Command Prompt

Below we will use the WHERE clause in the UPDATE command to update specified data in the example_tbl table.

The following example updates the example_title field value where example_id is 3 in the data table:

From the results, the example_title with example_id 3 has been modified.

---

Update Data Using PHP Script

In PHP, the function mysqli_query() is used to execute SQL statements. You can use or omit the WHERE clause in the SQL UPDATE statement.

Note:Not using the WHERE clause will update all data in the data table, so be careful.

This function has the same effect as executing the SQL statement in themysql>command prompt.

Example

The following example updates the data in the example_title field where example_id is 3.

Other extensions