SQL Update
What is Update in SQL?
In SQL Update used to modify existing records in table.
It allows you to change the values of one or more columns in one or more rows based on a specified condition.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
- table_name: The name of the table to update.
- column1 = value1, column2 = value2, ...: The columns and their new values that you want to set.
- WHERE condition: The condition that specifies which rows to update. If omitted, all rows in the table will be updated.
Examples:
Assuming we have a table named students with columns student_id, first_name, last_name, and age.
Example 1: Update a Single Column
UPDATE students
SET age = 21
WHERE student_id = 1;
This updates the age column to 21 for the student with student_id equal to 1.
Example 2: Update Multiple Columns
UPDATE students
SET first_name = 'Robert', last_name = 'Johnson'
WHERE student_id = 2;
This updates the first_name and last_name columns for the student with student_id equal to 2.
Example 3: Incrementing Values
UPDATE students
SET age = age + 1
WHERE last_name = 'Smith';
This increments the age by 1 for all students with the last name 'Smith'.
Example 4: Conditional Update
UPDATE students
SET age = CASE
WHEN age < 18 THEN age + 1
ELSE age
END;
This updates the age column, incrementing it by 1 for students with an age less than 18.
Example 5: Update All Rows
UPDATE students
SET age = age + 2;
This updates the age column for all students, incrementing it by 2.
Tips:
- Be cautious with the WHERE clause to ensure that you update the intended rows.
- Use the SET clause to specify the columns and their new values.
- Test your UPDATE statements with a SELECT statement before executing them to see which rows will be affected.