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
UPDATEcommand - 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
UPDATEcommand
Table of Contents
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.
Prerequisites
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:
# primary.py
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()
Faker.seed(42)
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
conn.commit()
cur.shut()
conn.shut()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 fakerIf 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: ThecustomerIDis the major key that helps us uniquely establish a report in a database desk.identify: The identify of the buyermetropolis: 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 thelow costsubject 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 clientsto 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, andnum_orders > 100filters 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
SET DISCOUNT=5
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
SET DISCOUNT=25;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.
Conclusion
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
UPDATEassertion sometimes consists of theWHEREclause with the situation that determines the subset of data (rows of the desk) to replace. - Omitting the
WHEREclause in theUPDATEassertion updates all the data. So you must watch out to not omit theWHEREclause 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.
