CODEWITHSUNDEEP
sql
aspire89
aspire89

Reputation: 535

How do I insert values into a table with a column-wise uniqueness check?

Create table

CREATE TABLE `my_table`
(
    id Uint64,
    name String,
    PRIMARY KEY (id)
);

Insert values

INSERT INTO `my_table`
    ( id, name )
VALUES (1, 'name1'),
       (2, 'name2'),
       (3, 'name3');
# id name
0 1 "name1"
1 2 "name2"
2 3 "name3"

How add VALUES (4, 'name1') and skip add VALUES (3, 'name1')?

The available syntax is described here: https://cloud.yandex.com/docs/ydb/yql/reference/syntax/insert_into

Upvotes: 1

Views: 375

Answers (1)

forpas
forpas

Reputation: 164099

From the documentation link that you provided in the comments I see that the databse that you use does not support a statement equivalent to INSERT OR IGNORE... to suppress errors if a unique constraint is violated.

As an alternative you can use INSERT ... SELECT.

If your database supports EXISTS:

INSERT INTO my_table
SELECT 3, 'name1'
WHERE NOT EXISTS (SELECT * FROM my_table WHERE id = 3);

Or you can use a LEFT JOIN:

INSERT INTO my_table
SELECT t.id, t.name
FROM (SELECT 3 AS id, 'name1' AS name) AS t
LEFT JOIN my_table AS m 
ON m.id = t.id
WHERE m.id IS NULL;

Upvotes: 1

Related Questions