SQL UPDATE: How to Update Database Tables

SQL UPDATE

What is SQL UPDATE?

In SQL, the UPDATE statement is used to modify existing records in a database table. It allows you to change one or more values in a table, based on certain criteria. Updating database tables is a crucial part of database management, as it allows you to keep your data up-to-date and accurate. In this blog, we will explore the different aspects of the SQL UPDATE statement, including how to use it to modify data in database tables.

In SQL, the UPDATE the statement is used to modify existing records in a table in a database. It allows you to change the values in one or more columns of a single row, multiple rows, or all rows in a table.

Here is the basic syntax of the UPDATE statement:

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

The SET the clause specifies the columns that you want to modify and the new values for those columns. The WHERE clause determines which rows will be updated. If you omit the WHERE clause, all rows in the table will be updated.

For example, suppose you have a customers table with the following data:

To update the city of Alice to ‘San Francisco’, you can use the following UPDATE statement:

UPDATE customers
SET city = 'San Francisco'
WHERE name = 'Alice';

This statement will modify the city column of the row with name ‘Alice’ to ‘San Francisco’. The resulting table will look like this:

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.

Top Keywords: SQL update syntax, SQL statement update, update query in SQL, SQL update query, update statement in SQL server, SQL server update statement, SQL update multiple columns, SQL-update multiple rows, SQL update multiple rows, SQL update with select, update table in SQL, SQL update selected.

Conclusion:

The SQL UPDATE statement is a powerful tool for modifying data in a database table. It allows you to change one or more values in a table, based on specific criteria and is an essential part of database management. However, it is important to use the UPDATE statement with caution, as incorrect or poorly executed updates can have serious consequences for your data. By understanding the syntax and best practices of the UPDATE statement, you can use it to keep your database tables up-to-date and accurate, without compromising the integrity of your data.