The SQL UPDATE Statements
The UPDATE statement in T-SQL (Transact-SQL) is used to update query modify existing records in a table. Here’s the basic syntax to update statement:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Let’s break down the components:
UPDATE: Keyword indicating that you want to update existing records.
table_name: The name of the table you want to update.
SET: Keyword indicating that you’re specifying which columns you want to update and the new values you want to assign to them.
column1, column2, …: The columns you want to update.
value1, value2, …: The new values you want to assign to the columns.
WHERE: Optional keyword used to specify a condition that determines which rows will be updated. If omitted, all rows in the table will be updated.
condition: The condition that must be met for a row to be updated. Only rows that satisfy this condition will be updated.
Here’s a simple example:
UPDATE Employees
SET Salary = 50000
WHERE Department = 'Finance';
This statement would update the “Salary” column of all employees in the “Finance” department to 50000.
Remember to always check and use the WHERE clause cautiously, as omitting it can result in a number of serious errors, unintended name errors and updates to all rows in the table.
How to Use UPDATE Query in SQL?
Updating a Single Column for All Rows:
This type of version of database update is useful when you need to apply the same change to update all rows in a table.
UPDATE Employees SET Department = ‘HR’;
This statement updates the “Department” column for all rows in the “Employees” table, setting them to ‘HR’.
Updating Multiple Columns:
Updating multiple columns allows you to modify various aspects of a row simultaneously.
UPDATE Students SET Grade = 'A', Status = 'Pass' WHERE Score >= 90;This statement updates the “Grade” and “Status” columns in the “Students” table for all students who scored 90 or above, setting their grade to ‘A’ and status to ‘Pass’.
Updating Based on a Subquery:
You can use a subquery to determine which rows should be updated based on some condition.
UPDATE Orders SET Status = 'Shipped' WHERE OrderID IN (SELECT OrderID FROM PendingOrders);This statement updates the “Status” column in the “Orders” table for orders that are pending (i.e., their OrderID exists in the “PendingOrders” table), setting their status to ‘Shipped’.
Updating with Calculated Values:
Calculated updates allow you to adjust column values based on expressions or calculations.
UPDATE Inventory SET Quantity = Quantity - 10 WHERE ProductID = 123;This statement updates the “Quantity” column in the “Inventory” table for the product with ID 123, subtracting 10 from its current quantity.
Updating Using Joins:
Joins enable you to update rows based on related data from other tables.
UPDATE Employees SET Department = Departments.NewDepartment FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID WHERE Employees.YearsOfService > 5;This statement updates the “Department” column in the “Employees” table for employees with more than 5 years of service, setting their department to the new department specified in the “Departments” table.
These examples illustrate different scenarios where the UPDATE query and syntax can be applied to update query modify data in SQL databases, offering flexibility and precision in data manipulation queries.
SQL Update Multiple Columns
Let’s create a new table called “Students” and insert some sample data. Then, I’ll demonstrate an example where we update multiple columns of records in the database using this table.
-- Create the Students table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Grade VARCHAR(2)
);
-- Insert sample data into the Students table
INSERT INTO Students (StudentID, FirstName, LastName, Age, Grade)
VALUES
(1, 'John', 'Doe', 18, 'B'),
(2, 'Jane', 'Smith', 20, 'C'),
(3, 'Michael', 'Johnson', 22, 'A'),
(4, 'Emily', 'Brown', 19, 'B');
-- Select the initial data from the Students table
SELECT * FROM Students;
Here are the results
Now, let’s say we want to update the database and check both the “Age” and “Grade” columns to check for details on a specific student (for example, StudentID = 2).
-- Update the Age and Grade columns for StudentID = 2
UPDATE Students
SET Age = 21,
Grade = 'A'
WHERE StudentID = 2;
-- Select the updated data from the Students table
SELECT * FROM Students;
After executing these SQL commands, the “Age” and “Grade” columns for the records in the record of the student with StudentID = 2 will be updated to 21 and ‘A’ respectively, and the rest of records in the data will remain unchanged.
Example – Update table with data from another table
Let’s create a new table called “StudentScores” to store the scores of each student. Then, I’ll demonstrate an example where we update the value of the “Grade” column in the “Students” table based on the average score of each student from the just created “StudentScores” table.
-- Create the StudentScores table
CREATE TABLE StudentScores (
StudentID INT,
Score INT
);
-- Insert sample data into the StudentScores table
INSERT INTO StudentScores (StudentID, Score)
VALUES
(1, 85),
(2, 92),
(3, 98),
(4, 79);
-- Select the initial data from the StudentScores table
SELECT * FROM StudentScores;
Now, let’s demonstrate how to update the “Grade” column in the “Students” records in a table below based on the average score of each student from the “StudentScores” records in a table below.
-- Update the Grade column in the Students table based on average score
UPDATE Students
SET Grade = CASE
WHEN (SELECT AVG(Score) FROM StudentScores WHERE StudentID = Students.StudentID) >= 90 THEN 'A'
WHEN (SELECT AVG(Score) FROM StudentScores WHERE StudentID = Students.StudentID) >= 80 THEN 'B'
WHEN (SELECT AVG(Score) FROM StudentScores WHERE StudentID = Students.StudentID) >= 70 THEN 'C'
ELSE 'F'
END;
-- Select the updated data from the Students table
SELECT * FROM Students;
In this example, we will update multiple columns in the “Grade” column in the “Students” table based on the average score of each student from the “StudentScores” table. Depending on the average score, we assign one table of different grades (‘A’, ‘B’, ‘C’, or ‘F’) to each student.
Update With A Join
Let’s use the same “Students” and “StudentScores” tables from the previous example and demonstrate how to update the “Grade” column in the “Students” table using a JOIN operation with the “StudentScores” table.
-- Create the Students table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Grade VARCHAR(2)
);
-- Insert sample data into the Students table
INSERT INTO Students (StudentID, FirstName, LastName, Age, Grade)
VALUES
(1, 'John', 'Doe', 18, NULL),
(2, 'Jane', 'Smith', 20, NULL),
(3, 'Michael', 'Johnson', 22, NULL),
(4, 'Emily', 'Brown', 19, NULL);
-- Create the StudentScores table
CREATE TABLE StudentScores (
StudentID INT,
Score INT
);
-- Insert sample data into the StudentScores table
INSERT INTO StudentScores (StudentID, Score)
VALUES
(1, 85),
(2, 92),
(3, 98),
(4, 79);
-- Select the initial data from the Students table
SELECT * FROM Students;
-- Select the initial data from the StudentScores table
SELECT * FROM StudentScores;
Now, let’s demonstrate how to update information in the “Grade” column in the “Students” table based on the average score of each student from each row in the “StudentScores” table using a JOIN operation:
-- Update the Grade column in the Students table based on average score using a JOIN
UPDATE Students
SET Grade = CASE
WHEN AVG(Score) >= 90 THEN 'A'
WHEN AVG(Score) >= 80 THEN 'B'
WHEN AVG(Score) >= 70 THEN 'C'
ELSE 'F'
END
FROM Students
INNER JOIN StudentScores ON Students.StudentID = StudentScores.StudentID
GROUP BY Students.StudentID;
-- Select the updated data from the Students table
SELECT * FROM Students;
In this example, we update the “Grade” column in the “Students” table based on the average score of each student from the “StudentScores” table using a JOIN operation. The UPDATE statement joins one table, the “Students” table with all the rows from “StudentScores” to one table, on the “StudentID” column and calculates the average score for each student. Then, it assigns a grade (‘A’, ‘B’, ‘C’, or ‘F’) based on the average score.
UPDATE With A Where Condition
Let’s use the same “Students” table and “StudentScores” table data from the previous examples. This time, I’ll demonstrate how to query and update the “Grade” column in the “Students” table based on a condition using the WHERE clause query syntax above.
-- Update the Grade column in the Students table based on a condition using the WHERE clause
UPDATE Students
SET Grade = 'A'
WHERE StudentID = 3;
-- Select the updated data from the Students table
SELECT * FROM Students; In this example, we update the “Grade” column in the “Students” table for a specific student, identified by the StudentID = 3, and set the value of their grade to ‘A’.
This UPDATE statement only affects the row(s) where the condition StudentID = 3 is true. In this case, it will update the value in the “Grade” column for the record of the student with StudentID = 3 to ‘A’.
Update with Aliases For Table Name
We’ll use the same syntax for “Students” and “StudentScores” tables as shown in example before and demonstrate how to update the “Grade” column in the “Students” table using an alias for table names.
-- Update the Grade column in the Students table based on average score using an alias
UPDATE s
SET Grade = CASE
WHEN AVG(sc.Score) >= 90 THEN 'A'
WHEN AVG(sc.Score) >= 80 THEN 'B'
WHEN AVG(sc.Score) >= 70 THEN 'C'
ELSE 'F'
END
FROM Students AS s
INNER JOIN StudentScores AS sc ON s.StudentID = sc.StudentID
GROUP BY s.StudentID;
-- Select the updated data from the Students table
SELECT * FROM Students; In this example:
We use aliases “s” for the “Students” table and “sc” for the “StudentScores” table to make the query more readable.
We update the “Grade” column in the “Students” table based on the average score of each student from the “StudentScores” table using an INNER JOIN operation.
We calculate the average score for each student using the AVG function and GROUP BY the student’s ID.
Then, we use a CASE statement to assign a grade (‘A’, ‘B’, ‘C’, or ‘F’) based on the average score.
This UPDATE statement will update the “Grade” column for each student in the “Students” table based on the changes to their average score from the last update table and “StudentScores” last update table below.
Additional Resources
Here is a good video on SQL Update statement
Here is a quick and easy way to execute test values and review the SQL UPDATE statement without installing SQL Server.
댓글