SQL
Update

UPDATE Statement

<sql> ::= <update_statement>

<update_statement> ::= "UPDATE" <table_name> "SET" <set_clause> ["WHERE" <condition>]

<set_clause> ::= <column_name> "=" <value> ["," <column_name> "=" <value>]*

<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 - UPDATE.

  2. UPDATE Statement (<update_statement>): An UPDATE statement starts with UPDATE, followed by a table name, a list of column-value pairs to set, and optionally a WHERE clause to filter rows.

  3. Components:

    • Set Clause (<set_clause>): Defines one or more column-value assignments used to update the table.
    • 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

UPDATE dictionary.users
SET name = &fields.name
WHERE id = &fields.id::NUMBER;

Example 2

UPDATE dictionary.users SET value = &value WHERE ref = &ref;

Example 3

UPDATE Dictionary.goods
SET name = "Teapot 33"
WHERE ref="5aa3999b-175c-0bf0-3267-ee5c623db38f";

Example 4

UPDATE Dictionary.storages
SET name = "Store 1", id = 1
WHERE ref="d9538dda-b990-be45-3b29-fd5ed13fb967";
InsertDelete