HexLibraryHexLibrary
</> HexCodeSign inSign up
PythonJavaScriptTypeScriptCC++JavaPHPGoRustRubyBashSQLLuaPerlCOBOLPascalDelphiFortranDartSwiftC#JuliaGroovyRElixirHaskellKotlinScalaClassic ASPClipperMS-DOS
LESSONS
Introduction to SQL (SELECT)Tables (CREATE & INSERT)Filtering (WHERE)Sorting & LimitingAggregate FunctionsUpdate & DeleteJoins (JOIN)
REFERENCE
📖 Overview
🔎 Querying (SELECT)
🗃️ Creating a Table
✏️ Inserting & Modifying Data
🧲 Filtering (WHERE)
🔢 Sorting & Limiting
📊 Aggregate Functions
🔗 Joins (JOIN)
SQL›Update & Delete

Update & Delete

Updating records with UPDATE and deleting with DELETE (the importance of WHERE).

Simple enough for a 5-year-old to understand

UPDATE

UPDATE without WHERE modifies ALL rows — be careful!

Example code
1
CREATE TABLE k(id INTEGER, name TEXT);
2
INSERT INTO k VALUES (1, 'Alex');
3
UPDATE k SET name='Alex L.' WHERE id=1;
Update id=1.
DatabaseUpdate id=1.
4
SELECT name FROM k;
Alex L.
Output / InputAlex L.
DatabaseOutput / Input
💡 Hint: Always restrict UPDATE and DELETE with WHERE; otherwise, it affects the entire table.

About this topic

UPDATE and DELETE are commands that modify existing data and are the most dangerous part of SQL. If you write a SELECT wrong, you see wrong results; if you write an UPDATE wrong, your data gets permanently corrupted. That is why there are some professional habits followed when writing these two commands.

The most important one is this: every UPDATE and DELETE comes with a WHERE. Forgetting the WHERE means changing ALL rows in the table — and this operation cannot be undone. The second habit is to write the query as a SELECT first to see how many rows it will affect.

The third protection is transactions: you start the change, check the result, and then confirm it; if something goes wrong, you roll it back. When working on production data, this is not an optional nuance but a standard practice.

What you will learn

  • updating specific rows with UPDATE
  • deleting rows with DELETE
  • why WHERE is vital in these commands
  • verifying the change with SELECT first
  • starting a transaction and rolling back or committing
  • updating multiple columns in a single query

When to use?

Fixing a user's profile info, updating an order's status, and cleaning up expired records are done with these commands.

Instead of permanent deletion, putting a "deleted" flag (soft delete) is preferred in many systems: it eliminates the risk of data loss and allows accidentally deleted records to be recovered.

Common mistakes

Wrong

UPDATE users SET city = 'Ankara';

Since there is no WHERE, the city of ALL users changes. This is the most classic SQL error leading to data loss and cannot be undone after the transaction is committed.

Correct

UPDATE users SET city = 'Ankara'
WHERE id = 42;

Wrong

DELETE FROM orders WHERE status = 'cancelled';

The query looks correct, but you don't know how many rows it will delete; if there is an unexpected state in the status value, thousands of records could be gone. Before deleting, you need to run a SELECT with the same condition and see the count.

Correct

SELECT COUNT(*) FROM orders WHERE status = 'cancelled';

DELETE FROM orders WHERE status = 'cancelled';

Wrong

UPDATE products SET price = price * 1.2
WHERE category = 'book';
UPDATE products SET stock = stock - 1
WHERE category = 'book';

If an error occurs between two separate queries, the data remains half-updated. Changes that carry meaning together should be made within a single transaction; thus, either all of them are applied or none.

Correct

BEGIN;

UPDATE products SET price = price * 1.2, stock = stock - 1
WHERE category = 'book';

COMMIT;

Frequently asked questions

Can I recover data that I accidentally deleted?

If the transaction has been committed and there is no backup, no. The only guarantee is regular backups. That is why using transactions when working on production data, verifying with SELECT first, and applying soft deletes on critical tables are standard protection methods.

What is the difference between DELETE and TRUNCATE?

DELETE deletes rows one by one, accepts WHERE, and can be rolled back within a transaction. TRUNCATE empties the entire table very quickly, but does not accept WHERE and cannot be rolled back in most systems. TRUNCATE is only used when you consciously want to completely empty a table.

How can I see how many rows were affected?

Database clients report the number of affected rows after UPDATE and DELETE. If this number is different from what you expected — especially if it is much larger — you must roll back the transaction immediately. That is why it is valuable to make critical changes within a transaction.

Summary

UPDATE and DELETE make irreversible data changes; both must be written with a WHERE clause. The professional habit consists of three steps: running a SELECT with the same condition first, making the change inside a transaction, and verifying the affected row count.

HexLibraryHexLibrary

Learn programming more clearly. Every command is explained line by line with colors, reinforced with runnable exercises, test your code instantly — all in one place, free.

Learn

  • Home
  • Languages & Lessons
  • HexCode

Company

  • About
  • Contact

Legal

  • Privacy Policy
  • Terms of Use

Other Projects

  • HexToolz
  • Free Service Manuals
© 2026 HexLibrary · The clearest way to learn code