SQL UPDATE: A Lesson on How to Update Database Tables

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.

In this article about the SQL Update command, we’ll be learning how to update existing records through the following topics:

  • What Is SQL UPDATE?
  • Syntax for SQL UPDATE Command
  • Updating Selected Records From a Single Column
  • Updating Selected Records From Multiple Columns
  • Updating All the Records From a Table’s Columns

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.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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: 

Employee_table

  • 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.

Employee_details

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:

one_column-SQL_UPDATE

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:

update_one_column_output

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:

mysql-update

This will result in the following table:

emp-id.

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:

Updating_multiple_column-SQL_UPDATE

This will result in the following table:

/Updating_multiple_columns_output.

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.

null_constarint_erro

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:

update_all_records-SQL_UPDATE

This will result in the following table:

update_all_records_output

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:

primary_key_error.

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.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.