SQL databases provide structured data storage capabilities. We can store data in tables with columns and rows. Other useful features for SQL databases are update capabilities. We can update SQL databases data in different ways and constraints. In this tutorial, we will learn how to update database table data with an SQL UPDATE statement and query single record, multiple records or conditional situations with examples.
SQL UPDATE Syntax
SQL Update statement or query has the following syntax with the given values.
update TABLE_NAME set COLUMN1=VALUE1, COLUMN2=VALUE2, ... , COLUMNN=VALUEN where CONDITION;
- `TABLE_NAME` is the table which we want to update its records.
- `COLUMN1` is the column name we want to update.
- `VALUE1` is the value we want to set the value.
- `COLUMN` and `VALUE` can be set for multiple times without a problem.
- `CONDITION` is used to select the record or row to update.
Example of Database Table Data
During this tutorial, we will use the following database table in order to update in different ways. We try to make the table simple in order to prevent misunderstanding and decrease complexity. In this table, we have the following columns. The table is named
|2||Ahmet Ali Baydan||Ankara||35||Turkey|
|3||Antonio Moreno Taquería||México||40||Mexico|
We will start with a simple update table example. We will only update the single record single value. We will update the age value of the Zlatan İbrahimovic by using its ID. We will set the age of the Zlatan İbrahimovic to the 38 .
UPDATE Users SET Age=38 WHERE ID=5;
Update Multiple Records
Update record can be used to update multiple records in a single Update query execution. We have to specify some conditions which will match multiple records on the given table and update the given columns. In this example, we will the Country of the users whose ages are over 30.
UPDATE Users SET Country='Turkey' WHERE Age>30;
Update Multiple Columns
We can update multiple columns for the given condition. Here we need to specify the column and value pairs by delimiting them with a comma. In this example, we will update the Country and City values of the users whose ages are over 30. We will set Country as
Turkey and City as
UPDATE Users SET Country='Turkey', City='Ankara' WHERE Age>30;
Update All Table Rows/Records For Specific Column
In some cases, we may need to update the whole table or all rows in a table for a specific column. In this case, we do not need a condition because we will match all table rows. In this example, we will set all users table records
Age value to the
UPDATE Users SET Age=30;
Update According To ID Column
During the application development, we generally write some SQL queries. The update SQL query is one of the most used ones. Almost every application have some tables which have the
ID column. We generally use ID values in order to update the table. In this example, we will show how to update according to ID.
UPDATE Users SET Age=30 WHERE ID=1;
OR we can update records those ID numbers higher than 3 .
UPDATE Users SET Age=30 WHERE ID>1;
Update Using BETWEEN Clause
Another useful scenario for the SQL Update is using with a BETWEEN clause or condition to match records to be updated. We can specify range by using
BETWEEN where the record values are between this range will be updated. In this example, we will update the
Age is between 30 and 40 .
UPDATE Users SET Country='Turkey' WHERE Age BETWEEN 30 AND 40;
Update Table From Other Table with SQL Select
Up to now we have updated data by providing explicitly and directly in a SQL query. In complex databases and applications, data can be provided from other tables. We can update data by fetching it from other tables by using the UPDATE SQL statement. We will use an external table named Cities where we will update the Country value of the Users table records according to the Cities table.
UPDATE Users SET Country=(SELECT Country FROM Cities WHERE Cities.City = Users.City) WHERE (SELECT Country FROM Cities WHERE Cities.City=Users.City);
Update Using SQL INNER JOIN
In the previous example, we have updated Users table from another table named Cities. We can do this by using an INNER JOIN SQL statement. The scenario is the same as the previous example. We will use an external table named Cities where we will update the Country value of the Users table records according to the Cities table.
UPDATE Users SET Users.Country = Cities.Country FROM Users INNER JOIN Cities ON Users.City =Cities.City;