UPDATE COMMAND

 The UPDATE command in SQL is used to modify existing records in a table. It allows you to update one or more rows by specifying a condition. If no condition is specified, all rows in the table will be updated (which should be done with caution).

1. Purpose

The UPDATE command is used to:

  • Modify specific columns for specific rows.
  • Change multiple columns at once.
  • Perform arithmetic or transformations on existing values.

2. Syntax

Basic Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name: The name of the table to update.
  • column1, column2, ...: Columns to modify.
  • value1, value2, ...: New values for the columns.
  • condition: Filters the rows to update. Without this, all rows are updated.

3. Examples

A. Update a Single Column

Update the salary of an employee with emp_id = 101:

UPDATE employees
SET salary = 60000
WHERE emp_id = 101;

B. Update Multiple Columns

Update the name and salary of an employee:

UPDATE employees
SET emp_name = 'Alice Johnson', salary = 65000
WHERE emp_id = 101;

C. Update Multiple Rows

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

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 20;

D. Update All Rows (Caution)

Set a default department ID for all employees:

UPDATE employees
SET department_id = 0;

⚠️ Warning: Without a WHERE clause, all rows will be updated.

E. Update Using Subqueries

Set the salary of an employee to match the department's average salary:

UPDATE employees
SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = 20)
WHERE emp_id = 101;

F. Update Using Calculations

Increase all salaries by a fixed amount:

UPDATE employees
SET salary = salary + 5000;

G. Update with Conditional Logic (CASE Statement)

Give a bonus to employees based on department:

UPDATE employees
SET salary = salary + 
  CASE 
    WHEN department_id = 10 THEN 5000
    WHEN department_id = 20 THEN 3000
    ELSE 2000
  END;

4. Key Points

A. WHERE Clause Importance

  • Without a WHERE clause, all rows are updated. Use the WHERE clause to narrow down the scope of changes.

B. Data Type Matching

  • Ensure the value being updated matches the column's data type. For example:
    • Numeric values should be assigned to numeric columns.
    • String values must be enclosed in single quotes ('value').

C. Transactions

  • By default, UPDATE is part of a transaction:
    • Use COMMIT to save changes permanently.
    • Use ROLLBACK to undo changes before committing.

D. Null Values

  • To set a column to NULL explicitly: 
    UPDATE employees
SET department_id = NULL
WHERE emp_id = 102;

5. Common Errors

A. ORA-00904: Invalid identifier

Occurs if a column name in the UPDATE statement is incorrect or doesn't exist.

B. ORA-01407: Cannot update to NULL

Occurs if you try to set a NOT NULL column to NULL.

C. ORA-00054: Resource busy

Occurs if another session locks the table being updated. Use FOR UPDATE to handle such cases.

6. Practical Example

Suppose we have a table employees:

emp_id emp_name department_id salary
101 Alice 20 50000
102 Bob 30 60000
103 Charlie 20 55000

A. Update a Single Row

Change Alice's department:

UPDATE employees
SET department_id = 40
WHERE emp_id = 101;

B. Update Multiple Rows

Increase salary for all employees in department 20:

UPDATE employees
SET salary = salary * 1.15
WHERE department_id = 20;

C. Verify Updates

To check if updates were successful:

SELECT * FROM employees;

7. Best Practices

A. Use Transactions

  • Always use COMMIT or ROLLBACK after updates to control changes: 
    UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 30;

COMMIT; -- Save changes
-- or
ROLLBACK; -- Undo changes

B. Backup Before Large Updates

  • Take a backup of the table or database before applying updates to critical data.

C. Test Queries

  • Test update queries on a smaller dataset or in a development environment before applying them to production.

8. Summary

Feature Details
Single Column Update SET column = value
Multiple Columns Update SET col1 = val1, col2 = val2
Update with Conditions Use WHERE to specify rows
Update with Subqueries Dynamically update using other queries
Update All Rows (Caution) Avoid unless intentional and necessary
Transactional Updates Use COMMIT and ROLLBACK for control


Comments

Post a Comment