SQL Formatter — INSERT / UPDATE / DELETE / MERGE

INSERT
Brick list mode for INSERT
Linked brick — column list and VALUES list
RETURNING INTO
UPDATE
DELETE
MERGE

INSERT

Both INSERT INTO … VALUES … and INSERT INTO … SELECT … forms are supported.

If the entire statement is short enough it is kept on one line (governed by the Simple statement max-length setting). Otherwise the formatter expands as follows:

INSERT INTO table(col-list) is placed on the first line. The opening ( follows the table name immediately. If the column list is short enough to fit on that line, it stays there with the closing ); otherwise each column appears on its own line indented one extra level, and the closing ) is on its own line at the INSERT INTO indent level.
VALUES starts on a new line, indented one level from the base. The opening ( follows VALUES immediately. If the value list is short enough it stays on that line; otherwise it is expanded the same way as the column list.
For the SELECT form, the subquery is formatted using the normal SELECT rules starting on the line after INSERT INTO.

Example — both lists short enough to stay on one line each:

INSERT INTO employees(employee_id, first_name, last_name, department_id, salary)
  VALUES (101, 'John', 'Smith', 10, 5000);

Example — standard expanded layout:

INSERT INTO employees(
    employee_id,
    first_name,
    last_name,
    department_id,
    salary
  )
  VALUES (
    101,
    'John',
    'Smith',
    10,
    5000
  );

When Brick list — Enabled is on and the column or value list contains enough items (controlled by Min items to activate), the formatter switches to brick layout for both the column list and the VALUES list.

In brick layout the items are packed into rows, each row starting at the column position of the first item and limited by Max line length and Max items per line.

Example — brick layout, max 3 items per row:

INSERT INTO employees (
    employee_id, first_name, last_name,
    department_id, job_id, hire_date,
    salary
  )
  VALUES (
    101, 'John', 'Smith', 10,  'DEV',
    DATE '2020-01-15', 5000
  );

Linked brick — column list and VALUES list

When Linked INSERT col / VALUES is on (default), the VALUES list wraps at exactly the same item positions as the column list. This means every value sits directly below its corresponding column name, making the relationship between columns and values immediately obvious at a glance.

Linked (default):

INSERT INTO employees (
    employee_id, first_name, last_name,
    department_id, job_id, hire_date,
    salary
  )
  VALUES (
    101, 'John', 'Smith',
    10, 'DEV', DATE '2020-01-15',
    5000
  );

Unlinked (each list wraps independently):

INSERT INTO employees (
   employee_id, first_name, last_name,
   department_id, job_id, hire_date,
   salary
  )
  VALUES (
    101, 'John', 'Smith', 10, 'DEV',
    DATE '2020-01-15', 5000
  );

Note: Linking requires that the column list and the VALUES list have the same number of items. When the counts differ (e.g. because DEFAULT or NULL padding was omitted), each list is formatted independently.

RETURNING INTO

RETURNING … INTO … clauses appear after INSERT, UPDATE, DELETE, and in the body of a MERGE action.

Three layout paths are used depending on total width:

Single line — if the entire RETURNING … INTO … clause fits within the RETURNING max length setting it is kept on one line.
Two-line — if the single-line limit is exceeded but each of the two sub-lists individually fits, the RETURNING list goes on one line and the INTO list on the next, each starting at the same indent level as the clause keyword.
Wrapped — if a sub-list is still too long, items within that sub-list are wrapped at the RETURNING max items limit.

Example — two-line layout:

UPDATE employees
  SET
    salary = salary * 1.1
  WHERE employee_id = 101
  RETURNING employee_id, first_name, last_name
  INTO :v_id, :v_fname, :v_lname;

UPDATE

Each SET assignment is placed on its own line, indented one level from SET. When Align SET assignments is enabled, the right-hand side of every assignment (= value) is padded so that all values align in a single column.

WHERE follows on its own line, with conditions expanded by the same AND / OR rules as in a SELECT statement.

Example:

UPDATE employees
  SET
    last_name     = 'Johnson',
    department_id = 20,
    salary        = 6000
  WHERE employee_id = 101;

DELETE

DELETE FROM table starts on the first line. WHERE starts on its own line, with conditions expanded using the same AND / OR rules as a SELECT statement.

Example:

DELETE FROM employees
WHERE department_id = 10
  AND hire_date < DATE '2000-01-01';

MERGE

MERGE statements are formatted with each top-level clause on its own line at the base indentation level. The body of each WHEN block is indented two levels from MERGE.

Clause layout:

MERGE INTO target USING source — USING starts on a new indented line when the full phrase is too long, otherwise stays on the same line.
ON (condition) starts on its own line, indented one level from MERGE.
WHEN MATCHED THEN and WHEN NOT MATCHED THEN each start on a new line at one level from MERGE.
UPDATE SET, INSERT, and DELETE bodies inside a WHEN block are indented a further level, with their sub-clauses following the same rules as standalone UPDATE and INSERT statements.
WHERE inside a WHEN MATCHED block is indented at the same level as UPDATE.

Example:

MERGE INTO employees t
  USING(SELECT employee_id, salary FROM new_salaries) s
  ON (t.employee_id = s.employee_id)
  WHEN MATCHED THEN
    UPDATE
    SET
      t.salary = s.salary
    WHERE t.salary <> s.salary
  WHEN NOT MATCHED THEN
    INSERT (employee_id, salary)
      VALUES (s.employee_id, s.salary);

LOG ERRORS / REJECT LIMIT clauses are formatted on their own line at the base indentation level.

← SELECT formatting | Formatting settings →