Decoding the SQL UPDATE Command 

Learn tips on how to use the SQL UPDATE command to replace data in a database desk.

As a developer, you have to be snug working with relational databases. SQL (or Structured Query Language) is a beginner-friendly language that’s intuitive to be taught and has a easy syntax. SQL permits you to create database objects and question relational database tables.

You can run SQL queries to create, learn, replace, and delete data in a database desk. You is perhaps acquainted that these operations are collectively known as CRUD operations. 

To replace the data saved in a database desk, you possibly can run the UPDATE command in SQL. 

In this tutorial, you’ll be taught:

  • The syntax of the UPDATE command 
  • How to replace data primarily based on a particular situation or a number of situations chained collectively utilizing logical operators 
  • Common caveat to concentrate on when operating the SQL UPDATE command 

Syntax of the SQL UPDATE Command

The syntax of the SQL UPDATE command is:

UPDATE table_name
SET column = new_value
WHERE situation;

The above question updates column for all data the place the situation is true.

To replace a number of columns (or fields), use the SQL UPDATE command like so:

UPDATE table_name
SET column_1 = new_value_1, column_2 = new_value_2, ..., column_k = new_value_k
WHERE situation;

Recall that in a relational database:

  • The desk represents an entity.
  • The rows of the desk are the data and symbolize an occasion of the entity.
  • The columns are additionally known as the fields or attributes. In this tutorial, we’ll use columns and fields interchangeably.

SQL UPDATE Command Usage Examples

Now let’s take some examples.


Before you begin coding:

  • This tutorial makes use of SQLite. So you’ll have to have SQLite and SQLite Database browser (beneficial) to code alongside. Note you could additionally use MySQL or PostgreSQL.
  • If you need to replicate the instance, you want Python and the Faker Python package as effectively.

Creating a Database Table with Records

If you’d prefer to code alongside to this tutorial, then you possibly can run the following code snippet to create and connect with a buyer database customer_db.db. Notice that we use the Faker Python library to generate artificial knowledge to insert into the clients desk:


import sqlite3
from faker import Faker
import random

# connect with the db
conn = sqlite3.join('customer_db.db')
cur = conn.cur()

# create a database desk
cur.execute('''CREATE TABLE clients (
                  customerID INTEGER PRIMARY KEY,
                  identify TEXT,
                  metropolis TEXT,
                  e mail TEXT,
                  num_orders INTEGER,
                  low cost INTEGER DEFAULT 2)''')

# create a Faker object 
pretend = Faker()

for _ in vary(15):
    identify = pretend.identify()
    metropolis = pretend.metropolis()
    d = pretend.domain_name()
    e mail = f"{name[:2]}.{city[:2]}@{d}"
    num_orders = random.alternative(vary(200))
    db_cursor.execute('INSERT INTO clients (identify, metropolis, e mail, num_orders) 
    VALUES (?,?,?,?)', (identify,metropolis,e mail,num_orders))

# commit the transaction 

For this code to work with out errors, be sure to have Python 3.7 (or a later model) and Faker put in in your growth setting. You can set up the Faker Python package deal utilizing pip:

pip set up faker

If you have already got a database you could connect with and a database desk you could question, you may as well use it as most popular.

Running Our First SQL Query

The clients desk has the following fields:

  • customerID: The customerID is the major key that helps us uniquely establish a report in a database desk.
  • identify: The identify of the buyer
  • metropolis: The metropolis they belong to.
  • e mail: Their e mail tackle.
  • num_orders: The variety of orders they’ve positioned.
  • low cost: The low cost proportion, an integer with a default worth of two. Because the low cost subject has a default worth, we don’t have to insert a worth for it when populating the database desk.

You can run the queries and look at the outcomes utilizing the SQLite command-line consumer. Or you should use the SQLite database browser.

I’ll current the outputs from the SQLite DB Browser as they’re straightforward to interpret.

Run the following question to get all the data in the clients desk:

SELECT * FROM clients;

In apply, you must keep away from utilizing SELECT * until it’s mandatory. But for this instance, we’ll use it as we’ve got solely 15 data and never many fields.

Updating Records Based on a Single Condition

Now that we all know what our desk seems to be like, let’s run a number of UPDATE queries to replace the data primarily based on the required situation.

Note: After operating the UPDATE statements, we’ll run SELECT * FROM clients to see the up to date data.

Updating a Single Field

First, let’s replace the metropolis subject of the report with customerID=7:

UPDATE clients
SET metropolis='Codeshire'
WHERE customerID=7;

Let’s choose all the columns and data from the clients desk:

SELECT * FROM clients;

We see that the metropolis subject of Danielle Ford (customerID=7) has been up to date.

Updating Multiple Fields

In the earlier instance, we up to date just one subject, particularly, the metropolis of the report similar to the customerID 7. But we are able to additionally replace a number of fields utilizing the syntax we’ve discovered.

Here we replace each the metropolis and the e mail subject similar to the customerID 1:

UPDATE clients
SET metropolis='East Carlisle',e mail='[email protected]'
WHERE customerID=1;

Next, we run:

SELECT * FROM clients;

And right here’s the output:


Updating Multiple Records

Because we used the customerID which is the major key that uniquely identifies a buyer report, the queries that we’ve run up to now up to date solely one among the data.

However, if the situation is true for multiple report in the desk, then operating the SQL replace command updates all the corresponding data.

Take this question for instance:

UPDATE clients 
SET low cost=10
WHERE num_orders > 170;

Now run this question:

SELECT * FROM clients;

Here’s the output:


Running the above SQL replace command modifies three data. They all have num_orders larger than 170 and now have a low cost worth of 10.

Updating Records Based on Multiple Conditions 

So far the WHERE clause had a easy situation, however it’s frequent for the filtering criterion to have a number of situations chained collectively by logical operators.

To perceive this, let’s set the low cost to five primarily based on two situations:

  • metropolis LIKE 'New%‘: this situation checks and consists of these data the place the metropolis subject begins with the New, and 
  • num_orders > 100 filters primarily based on the variety of orders to incorporate solely these data for which the variety of orders is larger than 100.

The UPDATE assertion seems to be like this:

UPDATE clients
WHERE metropolis LIKE 'New%' AND num_orders>100;

Notice that we now have two situations in the WHERE clause—chained collectively by the logical AND operator. And solely these data for which each situations maintain true are up to date.

Next run this question and observe the output:

SELECT * FROM clients;

As seen in the output, the low cost subject for these data for which each the above situations maintain true are up to date:


Common Caveat When Using the SQL UPDATE Command

In all the UPDATE statements executed to date, we included the WHERE clause.

For instance, you’d prefer to replace the low cost to 25 for a specific buyer. And in ypur replace question, you neglect to incorporate the WHERE clause with the customerID to filter on:

UPDATE clients

Now run:

SELECT * FROM clients;

You’ll see that every one the data in the desk are up to date. This might not be the conduct you’d need.


So bear in mind to incorporate the WHERE clause once you need to replace a subset of data primarily based on a situation. If you need to replace a specific subject for all the data, you possibly can omit the WHERE clause.

In this instance, say you could have the Black Friday sale arising, and also you need to supply all of your clients a reduction of 25%. Then operating the above question will make sense.


Here’s a abstract of what you’ve discovered:

  • When you need to replace data in a database desk, you should use the SQL UPDATE command.
  • The SQL UPDATE assertion sometimes consists of the WHERE clause with the situation that determines the subset of data (rows of the desk) to replace. 
  • Omitting the WHERE clause in the UPDATE assertion updates all the data. So you must watch out to not omit the WHERE clause if updating all the rows of the desk shouldn’t be the desired conduct.

Next, take a look at this SQL cheat sheet for fast reference.

Vikash Gorayan

Vikash Gorayan is a technology enthusiast with a passion for gadgets and product management. He is constantly seeking new opportunities to learn and grow in his field.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button