大查询-只有当列值不存在时才插入

时间:2022-10-12 19:19:45

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!

谢谢!

2 个解决方案

#1


4  

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

#2


2  

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
)

#1


4  

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

#2


2  

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
)