DELETE

SQL DELETE expressions allow you to remove rows from a database.

The delete function from Database.Beam.Query can be used to delete rows from a particular table. The function takes a table and a condition for the WHERE clause. The function returns a SqlDelete object that can be run in MonadBeam with runDelete.

For example, to delete any customer, whose first name is Emilio.

runDelete $ delete (customer chinookDb)
  (\c -> customerFirstName c ==. "Emilio")

DELETE
FROM "Customer" AS "delete_target"
WHERE ("delete_target"."FirstName") = ('Emilio');

DELETE
FROM "Customer"
WHERE ("FirstName")=(?);

-- With values: [SQLText "Emilio"];

DELETE is fairly simple compared to the other manipulation commands, so that's really all there is to it. Expressions for the WHERE clause can be arbitrarily complex, assuming your backend supports it.

For example, to delete any invoice with more than five invoice lines

Haskell
Sqlite

runDelete $ delete (invoice chinookDb)
  (\i -> 5 <. subquery_ (aggregate_ (\_ -> as_ @Int32 countAll_) $
                         invoiceLines i))

DELETE
FROM "Invoice"
WHERE (?)<(
             (SELECT COUNT(*) AS "res0"
              FROM "InvoiceLine" AS "t0"
              WHERE ("t0"."InvoiceId")=("InvoiceId")));

-- With values: [SQLInteger 5];

Note

The example above was only given for SQLite because it violates a foreign key constraint in the underlying database, and other backends are more pedantic