UPDATE in SQL

✏ī¸ The UPDATE statement in SQL is used to modify existing records in a database table. It allows you to change the values of one or more columns for specific rows or, if used without a filter, for every row in the table. UPDATE is one of the core Data Manipulation Language (DML) statements.

📖 What is the UPDATE Statement?

The UPDATE statement changes data that already exists in a table. You specify the columns to modify using the SET clause and typically use a WHERE clause to identify which rows should be updated.

Information

Unlike INSERT, which adds new rows, UPDATE modifies existing rows.

đŸŽ¯ Why Use UPDATE?

The UPDATE statement is essential whenever existing information needs to be corrected or changed.

  • 📌 Correct incorrect data.
  • 📌 Update employee salaries or student marks.
  • 📌 Modify customer contact information.
  • 📌 Change product prices.
  • 📌 Maintain accurate and up-to-date records.

📊 Sample Table

Consider the following Students table:

StudentIDNameDepartmentAgeMarks
101AliceComputer Science2092
102BobMathematics2185
103CharliePhysics2278

📝 Basic Syntax

UPDATE Syntax

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

💡 Update a Single Row

Update the marks of student 102.

Update One Record

UPDATE Students
SET Marks = 90
WHERE StudentID = 102;

Result:

StudentIDNameMarks
101Alice92
102Bob90
103Charlie78

📋 Update Multiple Columns

You can update multiple columns in a single statement.

Update Multiple Columns

UPDATE Students
SET Department = 'Data Science',
    Marks = 95
WHERE StudentID = 101;

📊 Update Multiple Rows

Use a condition that matches multiple rows to update all of them.

Update Multiple Records

UPDATE Students
SET Department = 'Engineering'
WHERE Department = 'Computer Science';

🌍 Update All Rows

If you omit the WHERE clause, every row in the table is updated.

Update Entire Table

UPDATE Students
SET Marks = 100;

Warning

Omitting the WHERE clause updates every row in the table. Always verify your query before executing it.

🔄 UPDATE Using Another Table

Many database systems support updating rows using data from another table, although the exact syntax varies between SQL implementations.

Update from Another Table (Example)

UPDATE Students s
SET Department = d.DepartmentName
FROM Departments d
WHERE s.DepartmentID = d.DepartmentID;

Important

The syntax for updating from another table differs across database systems. For example, SQL Server, PostgreSQL, MySQL, and Oracle each provide different approaches.

📈 UPDATE with Subquery

A subquery can be used to assign values dynamically.

Update Using a Subquery

UPDATE Employees
SET Salary =
(
    SELECT AVG(Salary)
    FROM Employees
)
WHERE Department = 'HR';

📊 UPDATE with CASE

The CASE expression allows different values to be assigned based on conditions.

Conditional UPDATE

UPDATE Students
SET Grade =
CASE
    WHEN Marks >= 90 THEN 'A'
    WHEN Marks >= 80 THEN 'B'
    ELSE 'C'
END;

⚖ī¸ INSERT vs UPDATE

FeatureINSERTUPDATE
PurposeAdds new rows.Modifies existing rows.
Creates New Records✅ Yes❌ No
Changes Existing Data❌ No✅ Yes

đŸ’ŧ Real-World Example

A university decides to increase the marks of all students in the Physics department by 5 points.

Increase Marks

UPDATE Students
SET Marks = Marks + 5
WHERE Department = 'Physics';

This statement updates only students in the Physics department while leaving all other records unchanged.

⚠ī¸ Common Mistakes

  • ❌ Forgetting the WHERE clause and updating every row.
  • ❌ Updating the wrong column.
  • ❌ Using an incorrect filter condition.
  • ❌ Assigning values with incompatible data types.

Warning

Before running an UPDATE statement, consider executing the same WHERE condition in a SELECT query first to verify which rows will be affected.

⚠ī¸ Best Practices

Best Practice

Always use a WHERE clause unless every row should be updated. Test the filter with a SELECT query first, use transactions for critical updates, back up important data before large modifications, and update only the necessary columns.

🚀 Key Points to Remember

  • 📌 UPDATE modifies existing records.
  • 📌 The SET clause specifies new values.
  • 📌 The WHERE clause determines which rows are updated.
  • 📌 Omitting WHERE updates every row.
  • 📌 Multiple columns can be updated in one statement.
  • 📌 Subqueries and CASE expressions can be used for advanced updates.
>>"The UPDATE statement keeps your database accurate by modifying existing information without creating new records."

Summary

✅ The UPDATE statement is a fundamental SQL command for modifying existing data. Whether correcting records, updating values based on conditions, or applying complex business rules, mastering UPDATE is essential for maintaining accurate and reliable databases.