DELETE with explicit scope
A wrong DELETE removes data that is hard to get back. Choose the rows with care, and look at what is left afterwards.
How it works
DELETE removes every row for which the WHERE condition is true. Each row that stays keeps all its values.
DELETE FROM table WHERE conditionremoves the rows the condition picks. EveryDELETEneeds aWHERE. To remove every row, writeWHERE TRUE.- Several conditions are joined with
ANDandOR.ANDis applied beforeOR, so parentheses show which conditions belong together. - A row stays when the condition is false or
NULL. A strict cutoff such as<leaves out the cutoff day itself. - A condition can look at another table with a subquery, as in
WHERE EXISTS (SELECT 1 FROM other_table AS o WHERE o.id = t.id). It removes the rows that have a partner in the other table.
Common mistake
Counting the rows you meant to remove cannot show that other rows went too. Read the whole table afterwards.
Worked example
CREATE TEMP TABLE example_sessions AS
SELECT 1 AS session_id, 'mobile' AS device, 1 AS pages
UNION ALL SELECT 2, 'desktop', 1
UNION ALL SELECT 3, 'mobile', 6;
DELETE FROM example_sessions WHERE device = 'mobile' AND pages <= 1;
SELECT session_id, device, pages FROM example_sessions ORDER BY session_id;Only session 1 meets both conditions, so it is deleted. Session 2 is on another device, and session 3 viewed more than one page.
Reading every remaining row shows that both other sessions survived. A count of the deleted sessions would not show that.