SQL : Updating Data

Objectives:

• Learn the UPDATE statement syntax
• Use SET to modify column values
• Apply conditions with the WHERE clause to specify which rows to update

1. Introduction to UPDATE Statement (5 minutes)

Syntax Overview:

• The UPDATE statement is used to modify existing records in a table.

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

Example:

UPDATE TestTable
SET Name = 'Jane Doe', DateOfBirth = '1995-01-01'
WHERE ID = 1;

2. Updating a Single Column (10 minutes)

Basic Syntax:

• Update a single column in a table.

  UPDATE Students
  SET FirstName = 'John'
  WHERE StudentID = 1;

Exercise:

• Write SQL queries to update the LastName of the student with StudentID 1 to ‘Doe’.

3. Updating Multiple Columns (10 minutes)

Syntax for Multiple Columns:

• Update multiple columns in a single statement.

  UPDATE Students
  SET FirstName = 'Jane', LastName = 'Smith'
  WHERE StudentID = 2;

Example:

UPDATE Students
SET FirstName = 'Emily', LastName = 'Johnson', EnrollmentDate = '2024-01-17'
WHERE StudentID = 3;

Exercise:

• Write SQL queries to:

1. Update FirstName to ‘Alice’ and LastName to ‘Brown’ for StudentID 4.
2. Change EnrollmentDate to ‘2024-01-20’ for StudentID 6.

4. Using WHERE Clause to Specify Rows (10 minutes)

Importance of WHERE Clause:

• The WHERE clause is crucial to prevent updating all rows.

  UPDATE Students
  SET EnrollmentDate = '2024-01-25'
  WHERE LastName = 'Smith';

Example:

UPDATE Students
SET FirstName = 'Charlie', LastName = 'Davis'
WHERE StudentID = 7;

Exercise:

• Write SQL queries to:

1. Update FirstName to ‘Kelly’ and LastName to ‘Lewis’ for students with EnrollmentDate after ‘2024-01-25’.
2. Change LastName to ‘Walker’ for all students whose FirstName is ‘Ian’.

5. Practical Exercise: Updating Data (10 minutes)

• Task:
• Use the Students table.
• Insert more sample data into Students table:

  INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
  VALUES 
      (16, 'Mona', 'Hall', '2024-01-29'),
      (17, 'Liam', 'Young', '2024-01-28');

• Exercises:

1. Update the FirstName to ‘Jack’ and LastName to ‘Green’ for StudentID 12.
2. Change EnrollmentDate to ‘2024-01-30’ for all students with LastName ‘Moore’.
3. Set FirstName to ‘Grace’ and LastName to ‘Wilson’ for StudentID 9.

Exercises

1. Updating a Single Column:

• Exercise: Write SQL queries to update the LastName to ‘Smith’ for StudentID 2.

1. Updating Multiple Columns:

• Exercise: Write SQL queries to:
  • Change FirstName to ‘John’ and LastName to ‘Doe’ for StudentID 1.
  • Update FirstName to ‘Alice’ and LastName to ‘Brown’ for StudentID 4.

1. Using WHERE Clause:

• Exercise: Write SQL queries to:
  • Update EnrollmentDate to ‘2024-01-25’ for all students with LastName ‘Smith’.
  • Change LastName to ‘Walker’ for all students with FirstName ‘Ian’.

1. Sample Update Tasks:

• Exercise: Execute the following tasks:
  1. Update FirstName to ‘Kelly’ and LastName to ‘Lewis’ for students with EnrollmentDate after ‘2024-01-25’.
  2. Change LastName to ‘Green’ for StudentID 12.

Homework:

• Practice Questions:
• Write SQL queries to:
  • Update LastName to ‘Moore’ for all students with FirstName ‘Hannah’.
  • Set EnrollmentDate to ‘2024-02-01’ for students whose LastName is ‘Evans’.