Explanation:

The UPDATE statement in SQL is used to modify existing records in a table. It is one of the crucial SQL commands, along with INSERT, DELETE, and SELECT, for manipulating data stored in a database.

Syntax:

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

• table_name: The name of the table you want to update.

• SET: This clause specifies columns and their new values.

• WHERE: This clause specifies which record or records should be updated. If you omit the WHERE clause, all records in the table will be updated!

Important: Always make sure to include a WHERE clause in the UPDATE statement to prevent updating all the records in the table.ca

Workshop Exercise:

Scenario:

Let's assume you have a table named Students with the following columns:

1. student_id: an auto-incrementing integer that serves as the primary key.

2. first_name: a varchar storing the student's first name.

3. last_name: a varchar storing the student's last name.

4. grade: an integer storing the student's grade in a course.

Create Students table:

CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    grade INT NOT NULL
);

Fill out the table with sample data:

-- Inserting sample student data
INSERT INTO Students (first_name, last_name, grade) VALUES
('John', 'Doe', 80),
('Jane', 'Smith', 85),
('Emily', 'Johnson', 90),
('Michael', 'Brown', 75),
('Sarah', 'Davis', 88);

Task:

Update the grade of a student named "John Doe" to 90.

Solution:

UPDATE Students
SET grade = 90
WHERE first_name = 'John' AND last_name = 'Doe';

Follow-up Exercise:

1. Insert Data:

   Insert a record for a student named "Jane Smith" with a grade of 89.

   Solution:

    INSERT INTO Students (first_name, last_name, grade)
    VALUES ('Jane', 'Smith', 89);
   

   

2. Update Multiple Fields:

   Update the first name to "Janet" and the grade to 88 for the student with the last name "Smith".

   Solution:

    UPDATE Students
    SET first_name = 'Janet', grade = 88
    WHERE last_name = 'Smith';
   

   

3. Safety First:

   Try updating the grade of a student without specifying a WHERE clause and see what happens. (Hint: It's a trick question, and you should NOT execute this query on a production database!)

   Solution:

    UPDATE Students
    SET grade = 90;
   

   Note: This will update the grade of every student to 90! Always use the WHERE clause judiciously.

   

Remember to always backup your data or work in a test environment when practicing!

Difference between UPDATE, ALTER and INSERT statements

SQL (Structured Query Language) provides various commands for manipulating and querying data stored in relational databases. Among them, UPDATE, ALTER, and INSERT are essential commands, each serving a distinct purpose. Let's understand the differences between them:

1. INSERT Statement:

• Purpose: The INSERT statement is used to add one or more new rows (records) to a table.

• Syntax:

    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);
  

• Example:

    INSERT INTO Employees (FirstName, LastName, Age)
    VALUES ('John', 'Doe', 30);
  

2. UPDATE Statement:

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

• Syntax:

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

  Always be cautious with the WHERE clause. If omitted, the update will apply to all rows in the table.

• Example:

    UPDATE Employees
    SET Age = 31
    WHERE FirstName = 'John' AND LastName = 'Doe';
  

3. ALTER Statement:

• Purpose: The ALTER statement is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.

• Syntax:

  • To add a column:

      ALTER TABLE table_name
      ADD column_name datatype;
    

  • To drop a column:

      ALTER TABLE table_name
      DROP COLUMN column_name;
    

  • To modify an existing column (e.g., change datatype or size):

      ALTER TABLE table_name
      MODIFY COLUMN column_name new_datatype;
    

• Example:

  • To add a new column "Email":

      ALTER TABLE Employees
      ADD Email VARCHAR(255);
    

  • To remove the "Email" column:

      ALTER TABLE Employees
      DROP COLUMN Email;
    

  • To modify the size of the "Email" column:

      ALTER TABLE Employees
      MODIFY COLUMN Email VARCHAR(500);
    

Summary:

• INSERT is used to add new rows to a table.

• UPDATE is used to modify data in existing rows.

• ALTER is used to modify the structure of the table itself (e.g., adding/removing columns or changing data types).

Each command plays a crucial role in the life cycle of a table, allowing for both the initial population of data (INSERT), the continuous evolution and correction of data (UPDATE), and structural adjustments to the table as requirements change (ALTER).

References

1. The SQL UPDATE Statement