Delete all Data from Table MySQL

TRUNCATE TABLE students; deletes every row fast and resets AUTO_INCREMENT. The table structure stays. It is DDL in MySQL — you usually cannot ROLLBACK a truncate like a DELETE in a transaction (engine/version details aside: treat TRUNCATE as ‘gone’).

DELETE FROM students; also removes all rows but logs each row, fires DELETE triggers, and can be rolled back in a transaction. Use DELETE when you need WHERE, triggers, or rollback. Use TRUNCATE when you want an empty table quickly in practice.

Trap: TRUNCATE on a table that other tables reference with FOREIGN KEY — InnoDB may refuse. Trap: thinking TRUNCATE is the same as DROP TABLE (DROP removes the table itself).

On the example next to this theory: Delete all Data from Table MySQL — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Diagram

  FROM students
        │
        ▼
  WHERE city = 'Pune'
        │
        ▼
  SELECT name, marks

Exam tip

TRUNCATE = empty table, keep columns. DELETE = can WHERE and rollback.

Delete all Data from Table MySQL — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

Delete all Data from Table MySQL — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefTRUNCATE removes all rows, keeps the table, resets AUTO_INCREMENT.
RuleDELETE if you need WHERE / rollback / triggers.
TrapTRUNCATE ≠ DROP TABLE.

Delete all Data from Table MySQL

Trap: TRUNCATE on a table that other tables reference with FOREIGN KEY — InnoDB may refuse. Trap: thinking TRUNCATE is the same as DROP TABLE (DROP removes the table itself).

On the example next to this theory: Delete all Data from Table MySQL — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Diagram

  FROM students
        │
        ▼
  WHERE city = 'Pune'
        │
        ▼
  SELECT name, marks

Exam tip

TRUNCATE = empty table, keep columns. DELETE = can WHERE and rollback.

Delete all Data from Table MySQL — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

Delete all Data from Table MySQL — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Short notes

DefTRUNCATE removes all rows, keeps the table, resets AUTO_INCREMENT.
RuleDELETE if you need WHERE / rollback / triggers.
TrapTRUNCATE ≠ DROP TABLE.

Delete all Data from Table MySQL

Delete all Data from Table MySQL — sample query

Short notes

Questions

Delete all Data from Table MySQL

Delete all Data from Table MySQL — sample query

Short notes

Questions