MySQL UPDATE

MySQL UPDATE Statement

The UPDATE statement in MySQL is used to modify existing records in a table. It allows you to update one or more rows based on specific conditions.

Syntax

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

table_name: The name of the table to update.
SET: Specifies the columns and their new values.
WHERE: Optional. Specifies the condition to determine which rows should be updated. Without it, all rows in the table will be updated.

Examples

1. Update a Single Row

Update the salary of an employee with id = 101:

UPDATE employees
SET salary = 6000
WHERE id = 101;

2. Update Multiple Rows

Increase the salary by 10% for all employees in the "Sales" department:

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

3. Update Multiple Columns

Change the name and age of a specific user:

UPDATE users
SET name = 'John Doe', age = 30
WHERE id = 1;

4. Update All Rows

Set a default value for a column in all rows:

UPDATE orders
SET status = 'Pending';

5. Update Using a Subquery

Update the salary of an employee based on the average salary of their department:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees WHERE department = 'HR')
WHERE id = 202;

Updating with Joins

You can update one table based on data from another table using a join.

Example: Update Data Based on a Join

Set the discount for customers who have placed orders worth more than $500:

UPDATE customers
JOIN orders ON customers.id = orders.customer_id
SET customers.discount = 10
WHERE orders.total > 500;

Updating with a LIMIT Clause

If you want to update only a certain number of rows, you can use the LIMIT clause.

Example: Update Only the First 5 Rows

UPDATE employees
SET salary = salary + 1000
WHERE department = 'Engineering'
LIMIT 5;

Safe Practices

1. Always Use WHERE Clause

Without a WHERE clause, all rows will be updated:

UPDATE employees
SET salary = 5000; -- Updates salary for all employees!

2. Test with a SELECT Statement

Before running an update, test the WHERE condition using a SELECT query:

SELECT * FROM employees WHERE department = 'Sales';

Checking the Number of Affected Rows

After executing an UPDATE query, MySQL provides the number of rows affected:

mysql> UPDATE employees SET salary = 7000 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

Best Practices

Backup Data: Always back up your database before performing bulk updates.
Use Transactions: For critical updates, use transactions to ensure consistency.
Test Queries: Run SELECT queries to verify your conditions before updating.

Let me know if you need help with specific UPDATE scenarios!

Souy Soeng