Decoding the SQL UPDATE Command 

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

As a developer, you ought to be conversant in working with relational databases. SQL (or Structured Question Language) is a beginner-friendly language that’s intuitive to be taught and has a easy syntax. SQL means that you can create database objects and question relational database tables.

You possibly can run SQL queries to create, learn, replace, and delete data in a database desk. Chances are you’ll be conversant in the truth that these operations are collectively known as CRUD operations.

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

On this tutorial you’ll be taught:

• The syntax of the UPDATE command
• Learn how to replace data based mostly on a particular situation or a number of circumstances linked collectively utilizing logical operators
• Normal caveats to remember 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 search above will likely be up to date column for all data the place the situation is true.

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

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

Take into account that in a relational database:

• The desk represents one entity.
• The rows of the desk are the data and characterize an occasion of the entity.
• The columns are additionally known as the fields or attributes. On this tutorial we use columns and fields collectively.

Examples of utilization of SQL UPDATE instructions

Now let’s take some examples.

Necessities

Earlier than you begin coding:

• This tutorial makes use of SQLite. So that you want SQLite and SQLite Database browser (really useful) to code with. Observe that you could additionally use MySQL or PostgreSQL.
• To duplicate the instance, you will additionally want Python and the Faker Python bundle.

Create a database desk of data

If you wish to code together with this tutorial, you may run the next code snippet to create and hook up with a buyer database customer_db.db. Observe that we’re utilizing the Faker Python library to generate artificial information that will likely be saved within the clients desk:

# fundamental.py

import sqlite3
from faker import Faker
import random

# hook up 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,
                  electronic mail TEXT,
                  num_orders INTEGER,
                  low cost INTEGER DEFAULT 2)''')


# create a Faker object 
faux = Faker()
Faker.seed(42)

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

# commit the transaction 
conn.commit()
cur.shut()
conn.shut()

📑 For this code to work flawlessly, ensure you have Python 3.7 (or a later model) and Faker put in in your growth surroundings. You possibly can set up the Faker Python bundle with pip:

pip set up faker

If you have already got a database that you could hook up with and a database desk that you could question, you can too use these as you want.

Run our first SQL question

The clients desk has the next fields:

• customerID: The customerID is the first key that enables us to uniquely determine a document in a database desk.
• identify: The identify of the client
• metropolis: Town they belong to.
• electronic mail: Their electronic 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. As a result of the low cost discipline has a default worth, we needn’t enter a price for it when populating the database desk.

📝 You possibly can run the queries and think about the outcomes utilizing the SQLite command line shopper. Or you should use the SQLite database browser.

I’ll current the output from the SQLite DB Browser as it’s simple to interpret.

Run the next question to get all data within the clients desk:

SELECT * FROM clients;

In observe, you need to keep away from utilizing it SELECT * until it’s mandatory. However for this instance we use it as a result of we solely have 15 data and never many fields.

Replace data based mostly on a single situation

Now that we all know what our desk appears to be like like, let’s take a look at a few of them UPDATE queries to replace the data based mostly on the required situation.

📋 Comment: After the UPDATE statements are executed, we’re executed SELECT * FROM clients to view the up to date data.

Replace a single discipline

Let’s first metropolis discipline of the document with customerID=7:

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

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

SELECT * FROM clients;

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

Replace a number of fields

Within the earlier instance, we solely up to date one discipline, the metropolis of the document equivalent to the customerID 7. However we are able to additionally replace a number of fields utilizing the syntax we discovered.

Right here we replace each the metropolis and the electronic mail discipline equivalent to the customerID 1:

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

Then we run:

SELECT * FROM clients;

And right here is the output:

Replace a number of data

As a result of we used the customerID That is the first key that uniquely identifies a buyer document. The searches we have carried out up to now solely up to date one of many data.

Nevertheless, if the situation applies to multiple document within the desk, operating the SQL replace command will replace all matching data.

Take this query for instance:

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

Now run this question:

SELECT * FROM clients;

This is the output:

Operating the SQL replace command above will change three data. All of them have num_orders higher than 170 and now have a low cost worth of 10.

Replace data based mostly on a number of circumstances

Till now the WHERE The clause had a easy situation, however it is not uncommon for the filter criterion to comprise a number of circumstances linked collectively by logical operators.

To know this, let’s set the low cost to five based mostly on two circumstances:

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

The UPDATE assertion appears to be like like this:

UPDATE clients
SET DISCOUNT=5
WHERE metropolis LIKE 'New%' AND num_orders>100;

Discover that we now have two circumstances within the WHERE clause: Linked collectively by the logical AND operator. And solely the data which have each circumstances are up to date.

Then run this question and see the output:

SELECT * FROM clients;

As may be seen within the output, the low cost discipline for the data the place each the above circumstances apply are up to date:

Normal caveat when utilizing the SQL UPDATE command

We now have included the WHERE clause in all UPDATE statements which were executed up to now.

For instance, you wish to replace the low cost for a specific buyer to 25. And in your replace question, you neglect the WHERE clause with the customerID filter by:

UPDATE clients
SET DISCOUNT=25;

Now run:

SELECT * FROM clients;

You will notice that each one data within the desk have been up to date. This is probably not the conduct you want to.

⚠ So do not forget to test the WHERE clause if you wish to replace a subset of data based mostly on a situation. If you wish to replace a specific discipline for all data, you may omit the WHERE clause.

On this instance, say you’ve got a Black Friday sale and wish to supply all of your clients a 25% low cost. Then it is sensible to run the above question.

Conclusion

This is a abstract of what you discovered:

• While you wish to replace data in a database desk, you should use the SQL UPDATE command.
• The SQL UPDATE assertion usually consists of the WHERE clause containing the situation that determines which subset of data (rows of the desk) to replace.
• Omitting the WHERE clause within the UPDATE assertion updates all data. So you must watch out that the WHERE clause if updating all rows of the desk isn’t the specified conduct.

Then try this SQL cheat sheet for a fast reference.