Table's Records Update Query in MySQL Workbench

• The `UPDATE` statement in MySQL Workbench is used to modify existing records in a table. It allows you to change the values of one or more columns in one or more rows based on specified conditions.

Here's a detailed explanation of how the `UPDATE` query works, along with examples:

• Basic Syntax:

    UPDATE table_name

    SET column1 = value1, column2 = value2, ...

    WHERE condition;

• Consider a "employees" table with the following data:

    CREATE TABLE employees (

        employee_id INT PRIMARY KEY,

        first_name VARCHAR(50),

        last_name VARCHAR(50),

        salary DECIMAL(10, 2)

    );

    INSERT INTO employees (employee_id, first_name, last_name, salary)

    VALUES

        (1, 'John', 'Smith', 50000.00),

        (2, 'Jane', 'Doe', 45000.00),

        (3, 'Michael', 'Johnson', 60000.00),

        (4, 'Emily', 'Brown', 52000.00),

        (5, 'David', 'Lee', 48000.00);

• Updating a Single Column: To update a specific column for a certain row:

    UPDATE employees SET salary = 55000.00 WHERE employee_id = 2;

• This query updates the "salary" column for the employee with "employee_id" 2.

• Updating Multiple Columns: To update multiple columns for a certain row:

    UPDATE employees SET first_name = 'Janet', salary = 48000.00 WHERE employee_id = 4;

• This query updates both the "first_name" and "salary" columns for the employee with "employee_id" 4.

• Updating Multiple Rows: To update multiple rows that meet a condition:

    UPDATE employees SET salary = salary * 1.1 WHERE salary < 55000.00;

• This query increases the salaries of employees earning less than 55000.00 by 10%.

• Updating with Subquery: You can use a subquery to update based on values from another table:

    UPDATE employees

    SET salary = (SELECT AVG(salary) FROM employees)

    WHERE department = 'Sales';

• This query updates the salaries of employees in the 'Sales' department to the average salary of all employees.

• Updating with CASE Statement: You can use a `CASE` statement within the `UPDATE` query for conditional updates:

    UPDATE employees

    SET salary = CASE

                    WHEN department = 'IT' THEN salary * 1.05

                    WHEN department = 'HR' THEN salary * 1.08

                    ELSE salary

                    END;

• This query updates salaries based on different multipliers for different departments.

• Updating All Rows: Be cautious when omitting the `WHERE` clause, as it will update all rows:

    UPDATE employees SET last_name = 'Johnson';

• This query updates the "last_name" column for all employees to 'Johnson'.

• The `UPDATE` query is a powerful tool for modifying existing records in your database. Always use the `WHERE` clause carefully to target the specific rows you intend to update.