Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL Update Statement
The UPDATE statement is used to modify existing records in a table. You can update one or more rows at a time based on specific conditions.
Basic Syntax
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
Note: Always use the WHERE clause to specify which records to update. Without it, all records in the table will be updated.
Example 1: Update a Single Record
Update the salary of a specific employee based on their employee_id.
Example:
Increase the salary of the employee with employee_id 5 by 10%:
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = 5;
Output:
If the previous salary of employee with employee_id 5 was 45000, it will now be updated to 49500.
employee_id | first_name | last_name | salary |
5 | Anil | Kapoor | 49500 |
Example 2: Update Multiple Records
Update the department of employees who are currently in ‘Marketing’ to ‘Sales’.
Example:
Change the department of all employees in ‘Marketing’ to ‘Sales’:
UPDATE employees
SET department = ‘Sales’
WHERE department = ‘Marketing’;
Output:
If there were multiple employees in ‘Marketing’, their department will now be ‘Sales’.
employee_id | first_name | last_name | department |
5 | Anil | Kapoor | Sales |
9 | Ananya | Kumar | Sales |
Example 3: Update with NULL Values
Update the age of employees who do not have a recorded age.
Example:
Set a default age of 30 for employees where age is NULL:
UPDATE employees
SET age = 30
WHERE age IS NULL;
Output:
If the employees with NULL age were updated, they will now have an age of 30.
employee_id | first_name | last_name | age |
12 | Meera | Patel | 30 |
Tips to Remember
- Use WHERE Clause Carefully: Always use the WHERE clause to target specific records. Omitting it will update all rows.
- Backup Data: Consider backing up your data before performing an update, especially if updating multiple rows.
- Test with SELECT: Before executing an UPDATE, run a SELECT statement with the same WHERE condition to see which rows will be affected.
- Transactions: Use transactions (BEGIN, COMMIT, ROLLBACK) to ensure data integrity and to undo changes if necessary.
Power of UPDATE Statement
The UPDATE statement:
- Modifies Existing Data: Allows you to correct or change existing records in the table.
- Supports Bulk Updates: Can update multiple rows based on specified conditions.
Preserves Data Integrity: Allows for targeted changes to maintain consistency in the database.
Frequently Asked Questions
Q1: What if I accidentally update the wrong records?
A1: Use ROLLBACK if you are using transactions, or restore from a backup if available.
Q2: Can I update data using values from another table?
A2: Yes, you can use a subquery to update records based on values from another table.
Example:
UPDATE employees
SET department = (SELECT new_department FROM departments WHERE departments.id = employees.department_id)
WHERE EXISTS (SELECT 1 FROM departments WHERE departments.id = employees.department_id);
Q3: How do I update data with complex conditions?
A3: Use AND, OR, and parentheses to build complex conditions in the WHERE clause.
Example:
UPDATE employees
SET salary = 55000
WHERE department = ‘IT’ AND age > 30;
Q4: Can I use UPDATE to set a column to NULL?
A4: Yes, you can set a column to NULL using the UPDATE statement.
Example:
UPDATE employees
SET salary = NULL
WHERE employee_id = 10;
Q5: How do I ensure that the update operation does not affect unintended rows?
A5: Carefully construct your WHERE clause to accurately target only the intended rows. Test with a SELECT query first to verify the affected rows.
