带有 WHERE 子句的 SQL Server INSERT INTO

本文介绍了带有 WHERE 子句的 SQL Server INSERT INTO的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用此查询将一些模拟付款信息插入开发数据库:

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);

推荐答案

我认为您正在尝试执行更新语句(为 ID = 145300 的客户设置金额 = 12.33)

I think you are trying to do an update statement (set amount = 12.33 for customer with ID = 145300)

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'