带有 WHERE 子句的 SQL Server INSERT INTO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48102013/
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
SQL Server INSERT INTO with WHERE clause
提问by Matt Larson
I'm trying to insert some mock payment info into a dev database with this query:
我正在尝试使用以下查询将一些模拟付款信息插入到开发数据库中：
INSERT
INTO
Payments(Amount)
VALUES(12.33)
WHERE
Payments.CustomerID = '145300';
How can adjust this to execute? I also tried something like this:
如何调整它以执行？我也尝试过这样的事情：
IF NOT EXISTS(
SELECT
1
FROM
Payments
WHERE
Payments.CustomerID = '145300'
) INSERT
INTO
Payments(Amount)
VALUES(12.33);
回答by Hadi
I think you are trying to do an update statement (set amount = 12.33 for customer with ID = 145300)
我认为您正在尝试执行更新语句（为 ID = 145300 的客户设置金额 = 12.33）
UPDATE Payments
SET Amount = 12.33
WHERE CustomerID = '145300'
Else if you are trying to insert a new row then you have to use
否则，如果您尝试插入新行，则必须使用
IF NOT EXISTS(SELECT 1 FROM Payments WHERE CustomerID = '145300')
INSERT INTO Payments(CustomerID,Amount)
VALUES('145300',12.33)
Or if you want to combine both command (if customer exists do update else insert new row)
或者，如果您想组合两个命令（如果客户存在，请更新否则插入新行）
IF NOT EXISTS(SELECT 1 FROM Payments WHERE CustomerID = '145300')
INSERT INTO Payments(CustomerID,Amount)
VALUES('145300',12.33)
ELSE
UPDATE Payments
SET Amount = 12.33
WHERE CustomerID = '145300'
回答by TheOni
If you want to insert new rows with the given CustomerID
如果要插入具有给定 CustomerID 的新行
INSERT
INTO
Payments(Amount,CustomerID )
VALUES(12.33,'145300');
else if you already have payment for the customer you can do:
否则，如果您已经为客户付款，您可以执行以下操作：
UPDATE
Payments
SET Amount = 12.33
WHERE
CustomerID = '145300';
回答by Frank F?rster
It sounds like having the customerID already set. In that case you should use an update statement to update a row. Insert statements will add a completely new row which can not contain a value.
听起来好像已经设置了 customerID。在这种情况下，您应该使用更新语句来更新一行。Insert 语句将添加一个不能包含值的全新行。
回答by lucky
Do you want to perform update;
是否要执行更新；
update Payments set Amount = 12.33 where Payments.CustomerID = '145300'
回答by Matt Larson
Ok, looks like I actually need to just do an insert into the Payments table that has the correct CustomerID, as there are currently no Payments with that CustomerID, so I cannot update it.
好的，看起来我实际上只需要插入具有正确 CustomerID 的 Payments 表，因为目前没有具有该 CustomerID 的 Payments，所以我无法更新它。
I ran INSERT INTO Payments (CustomerID, Amount, PaymentTypeID) Values ('145300', 24.99, 8);and then SELECT * FROM Payments WHERE Payments.CustomerID = '145300';to confirm and we're in business. Thanks everyone!
我跑INSERT INTO Payments (CustomerID, Amount, PaymentTypeID) Values ('145300', 24.99, 8);，然后SELECT * FROM Payments WHERE Payments.CustomerID = '145300';确认，我们的业务。谢谢大家！
回答by Vitor Gouveia
Better solution and without risk of deadlocks:
更好的解决方案并且没有死锁的风险：
UPDATE Payments
SET Amount = 12.33
WHERE CustomerID = '145300'
INSERT INTO Payments(CustomerID,Amount)
SELECT '145300',12.33
WHERE @@ROWCOUNT=0
回答by Ab Bennett
i do inserts into a table if the record doesn't exist this way. may not be entirely what is after but it may be helpful
如果记录不以这种方式存在，我会插入到表中。可能不完全是之后的但它可能会有所帮助
insert into x (a,b)
select 1,2
where 0=(select count(*) from x where a = 1 and b = 2)
