Edit

Delete Queries

Reference: Postgres, SQLite, MySQL, T-SQL, Oracle

Overview

Delete

Delete queries look like Select queries with an additional call to .delete.

sq.delete.from`person`.query
sq.from`person`.delete.query // equivalent

{ text: 'delete from person',
  args: [] }

.delete is idempotent.

sq`book`.delete.delete.delete.query

{ text: 'delete from book',
  args: [] }

From

.from works it does in Select queries.

However, be aware of certain SQL constraints Sqorn does not yet enforce.

  • Delete queries require exactly one, named table.
  • The table may not be a subquery or expression.
  • Joins are not allowed.

Reference more than one table by using:

  • Subqueries in the Where clause
  • With clause (CTE) join tables
  • Dialect-specific SQL extensions

Using

Postgres Only: The first .from call forms the delete clause. Subsequent .from calls form the using clause.

sq.delete
  .from`book`
  .from`author`
  .where`book.author_id = author.id and author.contract = 'terminated'`
  .query

{ text: "delete from book using author where (book.author_id = author.id and author.contract = 'terminated')",
  args: [] }

Where

Filter the rows to delete with .where.

sq.delete.from`person`.where`id = ${723}`.query

{ text: 'delete from person where (id = $1)',
  args: [723] }

.where works it does in Select queries.

Returning

Postgres Only: Return the deleted rows with .return.

sq.delete.from`person`.return`name`.query

{ text: 'delete from person returning name',
  args: [] }

Express

Express syntax works.

sq`person`({ job: 'student' })`name`.delete.query

{ text: 'delete from person where job = $1 returning name',
  args: ['student'] }
← SelectInsert →