As the world continually changes, so does data. It is crucial for database languages (like SQL) to provide updated information saved in database systems. This is achieved through the SQL UPDATE command, which is an integral part of this pervasive query language.
What Is SQL UPDATE?
SQL gives users the option to update existing records in tables with the help of the UPDATE command. Using this command, you can change and alter some (or all) of the records from single or multiple columns of a table.
This is a vital command as the data in any dataset keeps changing, so it helps the user keep datasets up-to-date.
Let’s get an insight into the syntax of the UPDATE command.
The Syntax for SQL UPDATE Command
|
UPDATE table_name SET column_1=value_1, column_2=value_2,... WHERE [condition]; |
- The UPDATE statement lets the database system know that you wish to update the records for the table specified in the table_name parameter
- The columns that you want to modify are listed after the SET statement and are equated to their new updated values. Commas separate these columns
- The condition in the WHERE clause dictates which rows from the mentioned columns will be updated
NOTE: It is crucial to remember that without the WHERE clause, all records from the specified columns will be updated.
Let’s try to update the rows from a single column in a table.
Updating Selected Records From a Single Column
For our example, we have a table that we’ll call “Employee,” as shown below:
- Before updating any of the records, it is important to check the data types of all columns. Additionally, if any of the columns have constraints (i.e.“Primary Key” and “NOT NULL”), the values need to be updated accordingly.
You can check this by using the DESC command.
As you can see, our table has the “Primary Key” constraint on the “EmployeeID” column, so it is important to remember not to update any records because it will create a duplicate value in this column.
The “Name” column has a “NOT NULL” constraint on it, so we cannot update any of the rows to a NULL value in either of these two columns.
- For example, if the Employee “Harry” changes his address, we will have to update the “City” attribute in our “Employee” table.
We’ll use the following query for this purpose:
We have enclosed the new “City” value in inverted commas since it is a character string data type—if not enclosed, the query will result in an error.
When executed, the query above will create the following table:
The photo above shows that the record has been updated from “Delhi” to “Chennai.”
- We can also update multiple records in a single column
For example, if all employees earning less than 30000 annually get raises, and their new salaries are now at 30000, we’ll use the following query to update these records in our table:
This will result in the following table:
These records have now been updated.
We will now update records from multiple table columns using a single query.
Updating Selected Records From Multiple Columns
Sometimes, we need to change record values from different columns in a table, and we can use the UPDATE command for this purpose as well.
- For example, if we need to update the city and salary details for an employee with an ID equal to four, we’ll use the following query in our table:
This will result in the following table:
This shows that the columns have been updated.
- It is imperative to keep the constraints and data types of each table column you’re updating in mind, and insert the new values according to those variables
For example, let’s see what happens when we try to update an employee's name as a NULL value.
This results in an error because the “Name” column has a NOT NULL constraint specified on it.
The WHERE clause is an important part of the UPDATE command. Let’s see what happens if this clause is not present in the query.
Updating All Records From a Table’s Columns
We should always be cautious while using UPDATE in SQL, as without the WHERE clause, all column rows specified in the query are updated to the new value. This can cause a significant problem if done unintentionally. Let’s look at an example of this type of record updating.
If we want to update all the rows of the “City” and “Salary columns, in our “Employee” table to the values “Bangalore” and “50000”, respectively, we’ll use the following query:
This will result in the following table:
All records in these columns have been updated to the specified values.
- Let’s see what happens when we try to change all the “EmployeeID” column records to the same value
We’ll use the following query for this purpose:
An error results, as the “EmployeeID” column is the Primary Key and cannot contain duplicate or NULL values.
With this, we come to the end of this article about the SQL UPDATE command.
Next Steps
It is crucial for datasets in database systems to stay up-to-date with the ever-changing, real-world data, and the UPDATE command in SQL enables users to do exactly that. You never have to worry about an outdated dataset. This command is a simple, yet powerful tool available to us.
Now that you know how to update existing records, it’s time for you to start querying and manipulating this updated and existing data in different datasets. This is an essential step to moving forward in your journey to becoming an SQL expert. If you liked this article, and want to get certified, check out Simplilearn’s Business Analyst Master’s Program. This comprehensive program covers SQL in-depth, and earning your certification in this field can help to jumpstart your career.
Do you have any questions for us? Please leave them in the comments section, and we’ll have our experts in the field answer them for you.