DELETE Queries

Builds DELETE queries using SqlDelete.

All SQL output below shows the result of get_sql(). Where queries have parameters, get_args() is shown alongside.

Basic Delete

The .where() method accepts the same parameter variants as SqlTerm.

qb = (SQL.delete('films')
      .where('kind', '<>', 'Musical'))

DELETE FROM films
WHERE kind <> %s

-- get_args(): ['Musical']

Delete All Rows

qb = SQL.delete('films')

DELETE FROM films

RETURNING

PostgreSQL only.

qb = (SQL.delete('tasks')
      .where('status', '=', 'DONE')
      .returning('*'))

DELETE FROM tasks
WHERE status = %s
RETURNING *

-- get_args(): ['DONE']

USING

The USING clause allows columns from other tables in the WHERE condition:

qb = (SQL.delete('films')
      .using('producers')
      .where(SQL.and_expr()
             .add('producer_id = producers.id')
             .add('producers.name', '=', 'foo')))

DELETE FROM films
USING producers
WHERE
    producer_id = producers.id
    AND producers.name = %s

-- get_args(): ['foo']

The same result using a subquery instead of USING:

qb = (SQL.delete('films')
      .where('producer_id', 'IN',
             SQL.select()
             .from_table('producers')
             .column('id')
             .where('name', '=', 'foo')))

DELETE FROM films
WHERE
    producer_id IN (
        SELECT id
        FROM producers
        WHERE name = 'foo'
    )

-- get_args(): ['foo']

DELETE ONLY

PostgreSQL only.

Restrict delete to the named table only (no inherited tables):

qb = (SQL.delete('parent_table')
      .only()
      .where('archived', '=', True))

DELETE FROM ONLY parent_table
WHERE archived = %s

-- get_args(): [True]

WITH Clause

qb = (SQL.delete('user_logs')
      .with_cte_query('delete_batch',
                      SQL.select()
                      .from_table('user_logs', 'l')
                      .column('l.ctid')
                      .where('l.status', '=', 'archived')
                      .order_by('l.creation_date')
                      .for_locking('UPDATE')
                      .limit(10000))
      .using('delete_batch AS del')
      .where('user_logs.ctid = del.ctid'))

WITH delete_batch AS (
    SELECT l.ctid
    FROM user_logs l
    WHERE l.status = %s
    ORDER BY l.creation_date
    LIMIT 10000
    FOR UPDATE
)
DELETE FROM user_logs
USING delete_batch AS del
WHERE user_logs.ctid = del.ctid

-- get_args(): ['archived']