Skip to content
go-jet edited this page Jan 19, 2022 · 3 revisions

DELETE statement deletes rows that satisfy the WHERE clause from the specified table. More about delete statement can be found at:
PostgreSQL: https://www.postgresql.org/docs/11/sql-delete.html
MySQL: https://dev.mysql.com/doc/refman/8.0/en/delete.html
MariaDB: https://mariadb.com/kb/en/library/delete/

Following clauses are supported:

  • USING(tables...) - allowing columns from other tables to appear in the WHERE condition (PostgreSQL, MySQL only)
  • WHERE(delete_condition) - only rows for which delete condition returns true will be deleted.
  • ORDER_BY() - rows are deleted in the order that is specified (MySQL, MariaDB, SQLite only)
  • LIMIT - maximum number of rows to be deleted. (MySQL, MariaDB, SQLite only)
  • RETURNING(output_expression...) - expressions to be computed and returned by the DELETE command after each row is deleted. The expression can use any column names of the table. Use TableName.AllColumns to return all columns. (PostgreSQL, SQLite only)

Example

// delete all links with name 'Gmail' and 'Outlook'
deleteStmt := Link.
    DELETE().
    WHERE(Link.Name.IN(String("Gmail"), String("Outlook")))

Debug sql of above statement:

DELETE FROM test_sample.link      -- test_sample is name of the schema
WHERE link.name IN ('Gmail', 'Outlook');

Execute statement

To execute delete statement and get sql.Result:

res, err := deleteStmt.Exec(db)

To execute PostgreSQL delete statement and return records deleted, delete statement has to have RETURNING clause:

deleteStmt := Link.
    DELETE().
    WHERE(Link.Name.IN(String("Gmail"), String("Outlook"))).
    RETURNING(Link.AllColumns)
    
dest := []model.Link{}

err := deleteStmt.Query(db, &dest)
    

Use ExecContext and QueryContext to provide context object to execution.

SQL table used for the example:
CREATE TABLE IF NOT EXISTS link (
    id serial PRIMARY KEY,
    url VARCHAR (255) NOT NULL,
    name VARCHAR (255) NOT NULL,
    description VARCHAR (255)
);