Skip to main content

DML Formatting

INSERT, UPDATE, DELETE, and CREATE TABLE statement formatting.

INSERT: INTO stays on same line as INSERT, VALUES on new line. Multiple VALUE rows use comma newlines. UPDATE: SET on new line with indented columns. Multiple SET columns use comma newlines. DELETE: FROM on new line, WHERE on new line. CREATE TABLE: Column list inside parens with one column per line.

INSERT VALUES​

insert into t (a, b) values (1, 2)
insert into t (a, b) values (1, 2)

INSERT SELECT​

insert into t select a from t2
insert into t select a from t2

UPDATE SET​

update t set a = 1 where b = 2
update t set a = 1 where b = 2

UPDATE SET multiple columns​

update t set a = 1, b = 2, c = 3 where id = 1
update t set a = 1, b = 2, c = 3 where id = 1

DELETE FROM​

delete from t where a = 1
delete from t where a = 1

INSERT VALUES multi-row​

insert into t (a, b) values (1, 2), (3, 4)
insert into t (a, b) values (1, 2), (3, 4)

INSERT VALUES single row unchanged​

Single row should look the same as multi-row format.

insert into t (a, b) values (1, 2)
insert into t (a, b) values (1, 2)

CREATE TABLE single column​

create table t (id int)
create table t (id int)

Nested subqueries (double nesting)​

select a from t where a in (select b from t2 where b in (select c from t3))
select a from t where a in (select b from t2 where b in (select c from t3))

Subquery in WHERE​

select a from t where a in (select b from t2)
select a from t where a in (select b from t2)

Subquery in FROM​

select a from (select b from t2) sub
select a from (select b from t2) sub

WITH CTE​

with cte as (select 1 as a) select a from cte
with cte as (select 1 as a) select a from cte

UNION ALL​

select a from t1 union all select b from t2
(select a from t1) union all (select b from t2)

MERGE basic​

MERGE statement with INTO, USING, ON, and WHEN clauses on new lines.

merge into t1 using t2 on t1.id = t2.id when matched then update set t1.a = t2.a when not matched then insert (a) values (t2.a)
merge into
    t1 using t2 on t1.id = t2.id
    when matched then update set t1.a = t2.a
    when not matched then insert (a) values(t2.a)

INSERT SELECT with WHERE​

INSERT...SELECT with a WHERE clause.

insert into t select a, b from t2 where x > 1
insert into t select a, b from t2 where x > 1

CREATE TABLE with NOT NULL and DEFAULT​

CREATE TABLE with column constraints.

create table t (id int not null, name varchar(100) default 'unknown', active boolean not null default true)
create table
    t (
      id int not null,
      name varchar (100) default 'unknown',
      active boolean not null default true
    )

Multiple CTEs​

Multiple CTEs with commas.

with cte1 as (select 1 as a), cte2 as (select 2 as b) select a, b from cte1, cte2
with cte1 as (select 1 as a), cte2 as (select 2 as b)
select a, b
from cte1, cte2

Messy INSERT with extra whitespace​

insert   into   t  ( a ,  b ,  c )  values  ( 1 ,  2 ,  3 ) , ( 4 ,  5 ,  6 )
insert into t (a, b, c) values (1, 2, 3), (4, 5, 6)

UPDATE SET with subquery​

Operator followed by opening paren should have a space: = ( not =(.

update t set a = (select max(b) from t2) where id = 1
update t set a = (select max(b) from t2) where id = 1

INSERT VALUES multi-row adaptive: preserves 3-per-line grouping​

insert into t (a) values (1), (2), (3),
(4), (5), (6),
(7)
insert into t (a) values (1), (2), (3), (4), (5), (6), (7)

INSERT VALUES multi-row adaptive: preserves 2-per-line grouping​

insert into t (a) values (1), (2),
(3), (4),
(5)
insert into t (a) values (1), (2), (3), (4), (5)

CREATE TABLE​

create table t (id int, name varchar(100) not null)
create table t (id int, name varchar (100) not null)