PartiQL DML

This page gives a brief overview of PartiQL’s DML statements.

INSERT

The insert statement creates new values (typically rows) in a table, with an optional ON CONFLICT action.

Examples

-- insert one
INSERT INTO table VALUES (v1, v2);

-- insert many
INSERT INTO table VALUES (v1, v2), (u1, u2);

-- insert one (explicit attributes)
INSERT INTO table (attr1, attr2) VALUES (v1, v2);

-- insert many (explicit attributes)
INSERT INTO table (attr1, attr2) VALUES (v1, v2), (u1, u2);

-- insert subquery
INSERT INTO table (SELECT v1, v2 FROM other)

-- upsert
INSERT INTO table VALUES (v1, v2) ON CONFLICT (attr1) DO UPDATE SET attr2 = v2;

-- replace
INSERT INTO table VALUES (v1, v2) ON CONFLICT DO REPLACE VALUE { 'attr1': v1, 'attr2': v2 };

In addition to the familiar SQL syntax, PartiQL also supports inserting a bag of values.

Inserting with BAGs

CREATE TABLE table1 ( items BAG<ANY> );
CREATE TABLE table2 ( attr1 STRING, attr2 STRING );

-- insert single bag value
INSERT INTO table1 VALUES (<< 1, 2, 3 >>);
INSERT INTO table1 VALUES (<< 1, 2, 3 >>), (<< 4, 5, 6 >>);

-- insert multiple values from a bag
INSERT INTO table2 <<
    { 'attr1': 'v1', 'attr2': 'v2' },
    { 'attr1': 'u1', 'attr2': 'u2' },
>>;

Inserting with Partial Schema

-- The following `CREATE TABLE` syntax is arbitrary since the PartiQL DDL is yet to be defined:
CREATE TABLE Foo /* SCHEMA OPEN */
(
   id         INT     NOT NULL PRIMARY KEY,
   is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
   title      VARCHAR(50),
   bar        VARCHAR(10)      DEFAULT 'baz',
);

-- In the following, inserting `{ 'id': 1 }` goes through because the rest of the attributes either have default value or
-- are nullable. In addition, because `Foo` has open-schema we can add `value` as a new attribute to the table.
INSERT INTO Foo
<<
{ 'id': 1 },
{ 'id': 2, 'title': 'some-name' },
{ 'id': 3, 'is_deleted': true, 'bar': '10'},
{ 'id': 4, 'title': 'some-other-name', 'value': '10'}
>>;

SELECT * FROM Foo;
<<
{ 'id': 1, 'is_deleted': false, 'title': NULL, 'bar': 'baz' },
{ 'id': 2, 'is_deleted': false, 'title': 'some-name', 'bar': 'baz' },
{ 'id': 3, 'is_deleted': true, 'title': NULL, 'bar': '10' },
{ 'id': 4, 'is_deleted': false, 'title': 'some-other-name', 'bar': 'baz', 'value': '10'},
>>;

For a complete grammar and specification, see INSERT

UPDATE

The update statement updates values (typically rows) of a table, with an optional WHERE condition.

Examples

-- update all rows
UPDATE table SET attr1=100, attr2=200;

-- update some rows
UPDATE table SET attr1=0 WHERE attr1 < 0;

-- update using value expressions (swap attr1, attr2)
UPDATE table SET attr1=attr2, attr2=attr1;

For a complete grammar and specification, see UPDATE

UPSERT

The upsert statement inserts values into a table, updating if there are any uniqueness constraint conflicts.

Examples

-- upsert one
UPSERT INTO table VALUES (v1, v2);

-- upsert many
UPSERT INTO table VALUES (v1, v2), (u1, u2);

-- upsert one (explicit attributes)
UPSERT INTO table (attr1, attr2) VALUES (v1, v2);

-- upsert many (explicit attributes)
UPSERT INTO table (attr1, attr2) VALUES (v1, v2), (u1, u2);

For a complete grammar and specification, see UPSERT

REPLACE

The replace statement inserts values into a table, replacing if there are any uniqueness constraint conflicts.

Examples

-- replace one
REPLACE INTO table VALUES (v1, v2);

-- replace many
REPLACE INTO table VALUES (v1, v2), (u1, u2);

-- replace one (explicit attributes)
REPLACE INTO table (attr1, attr2) VALUES (v1, v2);

-- replace many (explicit attributes)
REPLACE INTO table (attr1, attr2) VALUES (v1, v2), (u1, u2);

For a complete grammar and specification, see UPSERT

DELETE

The delete statement deletes values from a table, with an optional WHERE condition.

Examples

-- delete all
DELETE FROM table;

-- delete some
DELETE FROM table WHERE attr1 < 0;

For a complete grammar and specification, see DELETE