SQL 如何使用 where 子句插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/545593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert with where clause
提问by Quassnoi
I am using the following query to insert values into one field in table A.
我正在使用以下查询将值插入表 A 的一个字段中。
insert
into A (name)
values ('abc')
where
A.id=B.code
and B.dept='hr' ;
Is this query right? If not, please provide me a correct way.
这个查询对吗？如果没有，请给我一个正确的方法。
回答by htaler
You should rather use UPDATE, if you want to change the value of the field in records you select using the WHEREclause.
UPDATE如果要更改使用WHERE子句选择的记录中字段的值，则应该使用。
回答by Quassnoi
MERGE
INTO a
USING (
SELECT *
FROM b
WHERE b.dept = 'hr'
)
ON a.id = b.code
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (code, 'abc')
WHEN MATCHED THEN
UPDATE
SET name = 'abc'
回答by ShoeLace
the insert part should be ok
插入部分应该没问题
insert
into A (name)
values ('abc')
it really depends on what you are trying to achieve with the where clause and the B table reference.
这实际上取决于您尝试使用 where 子句和 B 表引用实现的目标。
回答by Harvy Derage
INSERT INTO A (name)
SELECT name
FROM A as a
LEFT JOIN B as b ON b.id=a.id
WHERE a.id=b.code AND b.dept='hr'
A is the first Table then B is the Second table JOIN together using LEFT JOIN then name is the column. this is what he wants to do on his question.
A 是第一个表，然后 B 是第二个表，使用 LEFT JOIN 连接在一起，然后 name 是列。这就是他想对他的问题做的事情。
This is what "INSERT SELECT STATEMENT" do.
这就是“插入选择语句”所做的。
Query that can Insert while doing a select and combining a WHERE and JOIN.
在执行选择并组合 WHERE 和 JOIN 时可以插入的查询。
INSERT TABLE1 (column1)
SELECT column1
FROM TABLE1 as a
LEFT JOIN TABLE2 as b ON b.column1=a.column1
WHERE a.column2=b.column3 AND b.column4='hr'
