Update statement

 The UPDATE statement in SQL is a powerful command that allows you to make changes to existing records in a database table. Imagine you have a table representing employees in a company. Each row in the table represents one employee, and each column represents a specific piece of information about them, such as their name, age, department, and salary.

update statement in detail


With the UPDATE statement, you can modify the values in one or more columns for specific rows based on certain conditions or criteria. For example, you might want to give a salary raise to all employees in the "Sales" department or change the job title of an employee who has been promoted.

The basic syntax of the UPDATE statement looks like this:

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

Here's what each part of the UPDATE statement means:

UPDATE: This keyword indicates that you want to update data in the table.

table_name: Replace this with the name of the table you want to update, such as "employees" in our example.

SET: After this keyword, you specify the columns you want to change and the new values you want to assign to them.

column1, column2, etc.: Replace these with the names of the columns you want to update, such as "salary" or "job_title."

value1, value2, etc.: Replace these with the new values you want to set for the corresponding columns. For example, if you want to increase the salary of all employees in the "Sales" department by 10%, you could set salary = salary * 1.1.

WHERE: This keyword is optional but essential. It allows you to specify a condition that determines which rows should be updated. If you omit the WHERE clause, all rows in the table will be updated, which can be risky if not intended.

condition: This is where you define the condition that filters the rows to be updated. For example, you could use WHERE department = 'Sales' to target employees in the "Sales" department.

Once you have written the UPDATE statement, the database engine will execute it, and the specified changes will be applied to the table. Remember to be careful when using UPDATE, as it directly modifies the data and can lead to unintended consequences if not used correctly.

Certainly! Let's delve further into the usage of the UPDATE statement in SQL:

Updating Multiple Columns:

The UPDATE statement allows you to update multiple columns within a single query. Simply include each column and its new value after the SET keyword, separated by commas.

Example:

Suppose you want to update both the salary and the job title of an employee with ID 101.

UPDATE employees
SET salary = 60000, job_title = 'Senior Developer'
WHERE employee_id = 101;

Updating Multiple Rows:

You can update multiple rows that meet a specific condition by using the WHERE clause appropriately. This can be done by specifying a condition that selects the rows you want to update.

Example:

Assume you want to give a salary raise of 5% to all employees who have been with the company for more than five years.

UPDATE employees
SET salary = salary * 1.05
WHERE years_of_service > 5;

Updating with Subqueries:

You can use subqueries in the UPDATE statement to update values based on data from another table or the same table.

Example:

Let's say you have a table named "employee_bonus" that contains the bonus amount for each employee. You want to update the bonus amount in the "employees" table based on the values from the "employee_bonus" table, matching the employee ID.

UPDATE employees
SET bonus_amount = (
    SELECT bonus
    FROM employee_bonus
    WHERE employee_bonus.employee_id = employees.employee_id
);

Using Joins in UPDATE:

Joins can also be used in conjunction with the UPDATE statement to update data from multiple tables at once.

Example:

Consider two tables, "employees" and "department," where the "department_id" column in the "employees" table references the "department_id" column in the "department" table. You want to update the department name for all employees.

UPDATE employees
SET employees.department_name = department.department_name
FROM department
WHERE employees.department_id = department.department_id;

Batch Updating with CASE:

The CASE statement can be helpful when you need to conditionally update a column based on different criteria.

Example:

Let's say you have a table named "orders" with a column "status" that represents the order status as 'pending,' 'shipped,' or 'delivered.' You want to update all 'pending' orders to 'shipped' if the delivery date is past today.

UPDATE orders
SET status = CASE
    WHEN delivery_date <= CURRENT_DATE THEN 'shipped'
    ELSE status
END
WHERE status = 'pending';

Using Transactions with UPDATE:

When you need to perform multiple UPDATE statements as a single, atomic operation, you can use transactions. Transactions ensure that all the updates either succeed together or fail together, maintaining data consistency.

syntax 

BEGIN TRANSACTION;

UPDATE table1
SET column1 = value1
WHERE condition1;

UPDATE table2
SET column2 = value2
WHERE condition2;

COMMIT;


Handling NULL values in an UPDATE

 Handling NULL values in an UPDATE statement is important to ensure data integrity and avoid unexpected behaviours. 

Example: 

Let's say you have a table called "students" with columns "student_id," "name," and "address." You want to set the "address" of a student with ID 101 to NULL.


UPDATE students
SET address = NULL
WHERE student_id = 101;

Update with select query


Using UPDATE with SELECT allows you to update a column in a table by fetching new values from another table based on a common column.

Example

Consider a table named "employees" with columns "employee_id," "first_name," "last_name," and "salary." You have another table named "salary_updates" with columns "employee_id" and "new_salary.


UPDATE employees
SET salary = (
    SELECT new_salary
    FROM salary_updates
    WHERE employees.employee_id = salary_updates.employee_id
);