Learning of SQL Day 9

 

Day 9: UPDATE Statement

Introduction:

• The UPDATE statement is used to modify existing records in a table. It allows you to change the data in one or more columns for rows that match specified conditions.

Key Concepts:

• Updating Data: Changing values in existing records.

• SET Clause: Specifies the columns and values to be updated.

• WHERE Clause: Specifies which rows to update.

SQL Commands and Examples:

1. Basic UPDATE Statement:

   • Update a single column for rows that meet a condition.

sql

-- Update the Age of the student with StudentID = 1
UPDATE Students
SET Age = 23
WHERE StudentID = 1;

2. Updating Multiple Columns:

   • Change values in multiple columns for specified rows.

sql

-- Update the FirstName and Age of the student with StudentID = 2
UPDATE Students
SET FirstName = 'Robert', Age = 24
WHERE StudentID = 2;

3. Using a Condition:

   • Use conditions to update specific rows.

sql

-- Increase the Age of all students by 1 year
UPDATE Students
SET Age = Age + 1
WHERE Age < 30;

Practice Exercise:

1. Use the Employees table created previously with columns EmployeeID, FirstName, LastName, Department, and Salary.

2. Write a query to update the Salary of an employee with EmployeeID = 1.

3. Write a query to update the Department of an employee with EmployeeID = 3.

4. Write a query to increase the Salary of all employees in the Sales department by 5000.

sql

-- Update the Salary of the employee with EmployeeID = 1
UPDATE Employees
SET Salary = 65000
WHERE EmployeeID = 1;

-- Update the Department of the employee with EmployeeID = 3
UPDATE Employees
SET Department = 'Marketing'
WHERE EmployeeID = 3;

-- Increase the Salary of all employees in the Sales department by 5000
UPDATE Employees
SET Salary = Salary + 5000
WHERE Department = 'Sales';

Important Tips:

• Always use the WHERE clause to specify which rows to update; otherwise, all rows in the table will be updated.

• Be cautious with updates to ensure data integrity and avoid unintended changes.

Mastering the UPDATE statement is crucial for maintaining and modifying your data accurately.