SQL UPDATE Statement and Query with Examples – POFTUT
SQL UPDATE Statement and Query with Examples

SQL UPDATE Statement and Query with Examples

by

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 database 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 a 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 in 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 it 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 Users.

  • ID
  • Name
  • City
  • Age
  • Country
IDNameCityAgeCountry
1Joachim LöwBerlin20Germany
2Ahmet Ali BaydanAnkara35Turkey
3Antonio Moreno TaqueríaMéxico40Mexico
4James BondLondon25UK
5Zlatan İbrahimovicLulea60Sweden

Update Table

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 records 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 Ankara.

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

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 has 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 Country to Turkey where 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 the 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;

LEARN MORE  How To Create a Database and Table In MySQL and MariaDB?

Leave a Comment