< Prev: UPDATE
Up: Query Building
Next: Building off a common query builder >

Query Building: DELETE

Contents

Basic example

  contents

Note: if you are using MySQL 8.0.16 and below you won't be able to alias your DELETE table (https://bugs.mysql.com/bug.php?id=89410).

SQL 
DELETE FROM public.users
WHERE email = 'bob@email.com';
Go 
u := tables.USERS()

// ...if you use ToSQL
query, args := sq.DeleteFrom(u).
    Where(u.EMAIL.EqString("bob@email.com")).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.DeleteFrom(u).
    Where(u.EMAIL.EqString("bob@email.com")).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

DELETE with join

  contents

In MySQL, if you are using JOINs I recommend you do not alias the table you are deleting from. Doing so will necessitate a different query structure, and that might trip you up. For completion, both examples (aliased and unaliased) will be shown below.

For Postgres, it doesn't matter. You can alias or not alias the table, the query structure stays the same.

SQL 
--------------
-- Postgres --
--------------

DELETE FROM public.users AS u
USING public.user_roles AS ur
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
WHERE u.user_id = ur.user_id AND urs.team_id = 15;

-----------
-- MySQL --
-----------

-- unaliased
DELETE FROM public.users
JOIN public.user_roles AS ur ON ur.user_id = users.user_id
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
SET u.name = 'bob', u.password = '1234'
WHERE urs.team_id = 15;

-- aliased
DELETE FROM u
USING public.users AS u
JOIN public.user_roles AS ur ON ur.user_id = u.user_id
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
SET u.name = 'bob', u.password = '1234'
WHERE urs.team_id = 15;
Go 
//----------//
// Postgres //
//----------//

u := tables.USERS().As("u")
ur := tables.USER_ROLES().As("ur")
urs := tables.USER_ROLES_STUDENTS().As("urs")

// ...if you use ToSQL
query, args := sq.DeleteFrom(u).
    Using(ur).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(u.USER_ID.Eq(ur.USER_ID), urs.TEAM_ID.EqInt(15)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.Update(u).
    Using(ur).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(u.USER_ID.Eq(ur.USER_ID), urs.TEAM_ID.EqInt(15)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

//-------//
// MySQL //
//-------//

// unaliased
u := tables.USERS()
ur := tables.USER_ROLES().As("ur")
urs := tables.USER_ROLES_STUDENTS().As("urs")

// ...if you use ToSQL
query, args := sq.DeleteFrom(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(urs.TEAM_ID.EqInt(15)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.DeleteFrom(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(urs.TEAM_ID.EqInt(15)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

// aliased
u := tables.USERS().As("u")
ur := tables.USER_ROLES().As("ur")
urs := tables.USER_ROLES_STUDENTS().As("urs")

// ...if you use ToSQL
query, args := sq.DeleteFrom(u).
    Using(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(urs.TEAM_ID.EqInt(15)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.DeleteFrom(u).
    Using(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(urs.TEAM_ID.EqInt(15)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

Multi-table DELETE

  contents

Note: Multi-table DELETE is a mysql-only feature.

Similar to multi-table UPDATE, MySQL can also do multi-table DELETE. However, I've found it to be of limited practicality due to foreign key constraints. The order of table deletion is not controllable, and the query optimizer may decide on an order that violates a foreign key constraint and cause the DELETE to fail. The MySQL docs mention that the only way to get around this is to do single-table DELETEs.

You will not be able to use the ORDER BY and LIMIT clauses in a multi-table DELETE.

SQL 
DELETE FROM u, ur, urs
USING public.users AS u
JOIN public.user_roles AS ur ON ur.user_id = u.user_id
JOIN public.user_roles_students AS urs ON urs.user_role_id = ur.user_role_id
WHERE urs.team_id = 15;
Go 
u := tables.USERS().As("u")
ur := tables.USER_ROLES().As("ur")
urs := tables.USER_ROLES_STUDENTS().As("urs")

// ...if you use ToSQL
query, args := sq.DeleteFrom(u, ur, urs).
    Using(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(urs.TEAM_ID.EqInt(15)).
    ToSQL()
_, err := DB.Exec(query, args...)
if err != nil {
    /* handle error */
}

// ...if you use Exec
_, err := sq.DeleteFrom(u, ur, urs).
    Using(u).
    Join(ur, ur.USER_ID.Eq(u.USER_ID)).
    Join(urs, urs.USER_ROLE_ID.Eq(ur.USER_ROLE_ID)).
    Where(urs.TEAM_ID.EqInt(15)).
    Exec(DB, 0)
if err != nil {
    /* handle error */
}

Clause manipulation

  contents
Clauses are just struct fields, you can either use method chaining to add clauses or manipulate the struct fields directly.
Clauses: WITH, DELETE FROM (postgres), DELETE FROM (mysql), USING, JOIN, WHERE, ORDER BY (mysql only), LIMIT (mysql only), RETURNING (postgres only),
< Prev: UPDATE
Up: Query Building
Next: Building off a common query builder >