17 Updating and deleting data
This chapter expands data manipulation beyond inserting rows to focus on changing and removing data with UPDATE and DELETE. It stresses that SQL changes in a relational database are executed in real time and are permanent—there is no save or undo—so careful filtering is essential. The text also notes practical safeguards like MySQL Workbench’s Safe Updates (which blocks broad, non-keyed changes) and frames each operation with an English-first approach that translates naturally into SQL.
For updates, SQL modifies values at the column level within rows. A valid UPDATE follows three parts in order: identify the target table (UPDATE), assign new values (SET), and supply a filter (WHERE)—the last being optional in syntax but critical in practice to avoid unintended, table-wide changes. You can update multiple columns, set values to NULL where allowed, and use variables for repeatable statements; keys or unique identifiers are preferred for precise targeting. When an update’s predicate spans multiple tables, you can join them in the statement, but syntax differs by RDBMS: MySQL uses UPDATE with JOIN before SET, PostgreSQL uses a FROM clause, and SQL Server incorporates a FROM join while keeping UPDATE/SET up front.
Deleting data is the inverse of inserting: DELETE removes one or more rows from a single table per statement, typically guarded by a WHERE clause to prevent wiping the entire table. Multi-table predicates are supported and, in engines like MySQL, SQL Server, and MariaDB, mirror a SELECT with joins by replacing the select list with DELETE and specifying the target alias. To remove all rows efficiently, TRUNCATE TABLE is faster than DELETE without a WHERE, though both can be constrained by database rules. The chapter’s key safety practice is to first run a SELECT that uses the exact same filtering logic as your UPDATE or DELETE, letting you preview the affected rows before making irreversible changes.
The Safe Updates, disabled in the Workbench Preferences
Lab answers
- Your query to insert a row in the customer table might look something like this:
- Your query to update your address in the customer table might look something like this:
- Your query to delete your row from the customer table might look something like this:
FAQ
Are data changes in a relational database immediate? Can I undo them?
Yes. Data manipulation statements (UPDATE, DELETE, INSERT, TRUNCATE) take effect immediately; there is no save/undo button. To stay safe, first run a SELECT that uses the exact same filtering you plan to use, confirm the intended rows, then execute the UPDATE or DELETE.What are the three essential parts of an UPDATE statement?
An UPDATE has, in this order: 1) the table to update (UPDATE), 2) the columns and new values (SET), and 3) an optional but critical filter (WHERE). Example:UPDATE title
SET Price = 8.95
WHERE TitleID = 101;
Can I update multiple columns at once?
Yes. List assignments in SET separated by commas. All changes apply to rows matching the same filter:UPDATE title
SET Advance = 0.00,
Royalty = 10.00
WHERE TitleID = 101;
Why is the WHERE clause so important in UPDATE and DELETE statements?
It limits which rows are affected. Omitting WHERE updates or deletes every row in the table. Always include and execute the correct WHERE, and validate it with a SELECT first.What is “Safe Updates” in MySQL Workbench and why might my UPDATE fail?
Safe Updates is a Workbench safety feature that, by default, prevents updates/deletes without a key-based filter to reduce accidental wide changes. If your tables lack keys, Workbench may block your statements. You can disable it in Edit > Preferences > SQL Editor > uncheck “Safe Updates” (restart Workbench). Re-enable it when you’re done to keep protection.How do I set a column to NULL?
Assign NULL in the SET list, but only if the column allows nulls:UPDATE author
SET MiddleName = NULL
WHERE AuthorID = 1;
Can I use variables to make repeatable UPDATE/DELETE statements?
Yes. Set variables, then use them in your statements:SET @TitleID = 101, @Price = 9.95;
UPDATE title
SET Price = @Price
WHERE TitleID = @TitleID;
How do I write an UPDATE that filters using multiple tables?
Syntax varies by RDBMS. In MySQL:UPDATE title t
INNER JOIN titleauthor ta ON t.TitleID = ta.TitleID
SET t.Price = 8.95
WHERE ta.AuthorID = 12;
How does DELETE work, and what happens if I omit WHERE?
DELETE removes rows from a single table. Example:DELETE
FROM title
WHERE TitleID = 110;
When should I use TRUNCATE TABLE instead of DELETE?
Use TRUNCATE TABLE to quickly remove all rows; it’s faster than DELETE without a WHERE because it doesn’t scan rows individually:TRUNCATE TABLE myfirstquery;
Learn SQL in a Month of Lunches ebook for free
