Changing Rows: UPDATE and DELETE

UPDATE changes the columns SET names and DELETE removes rows, each only in the rows its WHERE picks, rowcount says how many rows that was so an API can tell a change from a missing row, and a with block commits changes that belong together or rolls them all back.

Apprentice · SQLite · SQL · Database · Updated

By

After this lesson you can

  • Change and remove rows with UPDATE and DELETE limited by a WHERE, and use rowcount to tell whether a row was there
  • Explain what an UPDATE or DELETE without a WHERE does, and keep changes that belong together in a with block

Key takeaways

  • UPDATE ... SET ... WHERE changes the named columns in the rows the WHERE picks, and DELETE FROM ... WHERE removes those rows.
  • A cursor's rowcount says how many rows changed, so 0 means no row had that id, which an API answers with a 404.
  • An UPDATE or DELETE with no WHERE changes or removes every row in the table, and the database doesn't ask first.
  • Running a change's WHERE as a SELECT first shows exactly which rows it will touch.
  • A with conn block commits its changes when it finishes and rolls all of them back when it raises an error.

Data doesn't stay put: a price goes up, a customer changes their email, an order is canceled, a product is discontinued. Behind every edit button and every delete button in an app there's an UPDATE or a DELETE. They're short statements, and the most dangerous ones you'll write, so this lesson also covers the habits that keep them safe.

Fern & Fig has two jobs today: the Boston Fern's price goes up to $19.50, and the Golden Pothos is discontinued.

UPDATE: change the rows WHERE picks

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE plants (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL)")
conn.executemany(
    "INSERT INTO plants (name, price) VALUES (?, ?)",
    [("Snake Plant", 24.0), ("Spider Plant", 12.5), ("Boston Fern", 18.0), ("Golden Pothos", 15.0)],
)

cursor = conn.execute("UPDATE plants SET price = ? WHERE id = ?", (19.5, 3))
print("Changed:", cursor.rowcount)
cursor = conn.execute("UPDATE plants SET price = ? WHERE id = ?", (19.5, 99))
print("Changed:", cursor.rowcount)
conn.commit()

print(conn.execute("SELECT name, price FROM plants WHERE id = ?", (3,)).fetchone())
Changed: 1
Changed: 0
('Boston Fern', 19.5)

Read the statement as "update plants, set the price to 19.5, where the id is 3". SET names each column to change and its new value (SET price = ?, name = ? changes two), and WHERE picks the rows, the same way it does in a SELECT. The values come in through ? placeholders, as always.

The cursor's rowcount says how many rows the statement changed. The second UPDATE asked for plant 99, which isn't there, so nothing changed and rowcount is 0. That's how an API tells "done" from "there's no such plant": 1 means answer with the plant, 0 means answer 404.

DELETE: remove the rows WHERE picks

cursor = conn.execute("DELETE FROM plants WHERE id = ?", (4,))
print("Removed:", cursor.rowcount)
cursor = conn.execute("DELETE FROM plants WHERE id = ?", (4,))
print("Removed:", cursor.rowcount)
conn.commit()
print(conn.execute("SELECT id, name FROM plants").fetchall())
Removed: 1
Removed: 0
[(1, 'Snake Plant'), (2, 'Spider Plant'), (3, 'Boston Fern')]

"Delete from plants where the id is 4": the Golden Pothos is gone. Deleting it again removes nothing, and rowcount says so. A DELETE removes whole rows; to empty one column, UPDATE it to NULL instead.

The WHERE you forgot

Here is the most famous mistake in SQL. Someone means to put one plant on sale and leaves out the WHERE:

cursor = conn.execute("UPDATE plants SET price = ?", (9.99,))
print("Changed:", cursor.rowcount)
print(conn.execute("SELECT name, price FROM plants").fetchall())
Changed: 3
[('Snake Plant', 9.99), ('Spider Plant', 9.99), ('Boston Fern', 9.99)]

With no WHERE, every row is picked, so every plant now costs $9.99, and a DELETE FROM plants with no WHERE would empty the table. The database doesn't ask "are you sure?". Three habits keep it from happening to you:

  • Write the WHERE first, before the SET, when you type an UPDATE or DELETE by hand.
  • Run the WHERE as a SELECT first. SELECT * FROM plants WHERE id = 3 shows exactly the rows the change will touch.
  • Check rowcount. Changing one plant should change one row. Any other number is a bug.

Changes that belong together

Sometimes two changes only make sense together. For a weekend sale, Fern & Fig takes $5 off two plants. If the program crashes after the first UPDATE, the shop has half a sale. A transaction is a group of changes that are saved together or not at all, and in Python, a with conn: block is one: when the block finishes, the connection commits, and when it raises an error, the connection rolls back, undoing every change since the last commit.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE plants (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL)")
conn.executemany("INSERT INTO plants (name, price) VALUES (?, ?)", [("Snake Plant", 24.0), ("Spider Plant", 12.5)])
conn.commit()

try:
    with conn:
        conn.execute("UPDATE plants SET price = price - ? WHERE id = ?", (5.0, 1))
        conn.execute("UPDATE plants SET price = price - ? WHERE id = ?", (5.0, 2))
        raise ValueError("the register crashed")
except ValueError as error:
    print("Stopped:", error)

print(conn.execute("SELECT name, price FROM plants").fetchall())
Stopped: the register crashed
[('Snake Plant', 24.0), ('Spider Plant', 12.5)]

Both UPDATEs ran, then the error rolled them back: the prices are what they were. Without the error, both would have been saved together. (SET price = price - ? works out the new value from the old one, read "set price to price minus five".) A Database Connection for Every Request keeps each API request's changes together the same way.

Do it: reprice one plant

Fern & Fig's staff app has a box for changing one plant's price. It needs to know whether the plant was there.

  • conn: an open connection to a database whose plants table has the columns id, name and price.
  • plant_id: the id of the plant to change, a whole number.
  • price: its new price, such as 16.0.

Your job: write the function reprice(conn, plant_id, price).

  • Change the price of the plant with that id, and only that plant, with an UPDATE that uses ? placeholders.
  • Commit the change.
  • Return how many rows changed: 1 when the plant was there, 0 when no plant has that id.

Worked through: in the four plants from this lesson's first example, the Golden Pothos is plant 4. reprice(conn, 4, 16.0) changes its price from 15.0 to 16.0 and returns:

1

Practiced live on the lesson page: write the Python right there and check it.

Check yourself

2 questions to try on the lesson page.

So what

Changing and removing data is where a small slip does the most damage, and a WHERE on every UPDATE and DELETE, a look at rowcount and a with block for changes that belong together keep the damage from happening.

Key terms

Related