CODEWITHSUNDEEP
sqlgoogle-app-enginegoogle-bigquery
SupimpaAllTheWay
SupimpaAllTheWay

Reputation: 1468

Big Query - Only insert if column value does not exist

Does Big Query support operations like "REPLACE INSERT" or something related to that?

If I run a query like this twice:

INSERT INTO table(column1) VALUES(1)

It'll create a duplicated row, is it possible to insert a row only if a column with the same value does not exist?

Thanks!

Upvotes: 9

Views: 13646

Answers (2)

Yunxiao Ma
Yunxiao Ma

Reputation: 152

Does this work for you?

INSERT INTO table(column1)
WITH s AS (SELECT 1 src)
SELECT src FROM s WHERE NOT EXISTS (
  SELECT * FROM table t WHERE t.column1 = s.src
)

Upvotes: 6

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Below should make it

#standardSQL
INSERT INTO yourTable(column1)
SELECT value FROM (SELECT 1 AS value) 
LEFT JOIN yourTable  
ON column1 = value
WHERE column1 IS NULL

Upvotes: 7

Related Questions