MySQL DELETE Statement

Using MySQL delete state you can delete table data. With a single delete statement you can delete one or more tables data at a time.

Example of MySQL DELETE:

DELETE FROM users WHERE status = 0;

For multiple table:

DELETE users, orders
FROM users, orders
WHERE users.user_id = orders.user_id
AND users.status = 0;

TRUNCATE TABLE

MySQL TRUNCATE TABLE allows you to delete all data from a table. There is no condition in TRUNCATE TABLE clause.

TRUNCATE TABLE tbl_name;

DELETE JOIN

Same as UPDATE clause you can delete using JOIN query.

DELETE users, orders
FROM orders
INNER JOIN users
ON orders.user_id = users.user_id
WHERE orders.status = 0

ON DELETE CASCADE

Using MySQL ON DELETE CASCADE you can delete data from child tables automatically when you delete the data from the parent table depends on foreign key.

While creating a table you have to use this syntax:

CREATE TABLE user (
id INT NOT NULL,
name VARCHAR(100),
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE marks (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;