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.

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.

All BigQuery lessons