SQL
Delete

DELETE Statement

<sql> ::= <delete_statement>

<delete_statement> ::= "DELETE" ["PERMANENTLY"] "FROM" <table_name> ["USING" <table_list>] ["WHERE" <condition>]

<table_list> ::= <table_name> ["," <table_name>]*

<condition> ::= <expression> ["AND" <expression>]*
<expression> ::= <column_name> <operator> <value>
<operator> ::= "=" | "<>" | "<" | ">" | "<=" | ">=" | "IS NOT NULL"

<table_name> ::= [a-zA-Z_][a-zA-Z0-9_.]*
<column_name> ::= [a-zA-Z_][a-zA-Z0-9_]*
<value> ::= <literal> | <parameter>
<literal> ::= '"[^"]*"' | "'[^']*'" | [0-9]+
<parameter> ::= "&" [a-zA-Z_][a-zA-Z0-9_]*

Explanation

  1. SQL Statement Types (<sql>): The BNF defines the primary type of SQL statement - DELETE.

  2. DELETE Statement (<delete_statement>): A DELETE statement starts with DELETE, optionally followed by PERMANENTLY for delete records physically, followed by FROM and a table name. Optionally, it may include a USING clause to specify additional tables involved in the deletion and a WHERE clause to filter rows.

  3. Components:

    • Table List (<table_list>): Defines one or more tables used in the USING clause.
    • Condition (<condition>): Specifies filtering conditions for WHERE clauses.
    • Expression (<expression>): Represents a conditional expression that compares a column with a value using an operator.
    • Operators (<operator>): Defines the comparison operators, such as =, <>, <, >, <=, >=, and IS NOT NULL.

  4. Table and Column Naming:

    • Table Name (<table_name>): Represents the name of the table, following typical SQL naming conventions.
    • Column Name (<column_name>): Represents the name of a column, following typical SQL naming conventions.

  5. Values:

    • Literal (<literal>): Represents a value such as a string or number.
    • Parameter (<parameter>): Represents a parameter that starts with &.

Example 1

delete PERMANENTLY from dictionary.events
where id=100;

Example 2

delete PERMANENTLY from Contract.tokenminterc20token;

Example 3

delete from dictionary.users where ref = &ref::UUID;

Example 4

DELETE FROM Operation.operation_order
WHERE ref in ("58b62115-6f06-abc1-225f-e98fb8c9582c", "621b8392-4614-264a-6d43-87b99366ae84", "2e1d0a11-8874-bb66-f694-ca7e3a33f4f1");

Example 5

DELETE FROM Operation.operation_order
WHERE ref = "3b0a32dd-a977-4b29-8ccf-ca031b5f7147";
UpdatePlayground