SQL Server INSERT INTO 带 WHERE 子句

Question

我正在尝试使用以下查询将一些模拟支付信息插入到开发数据库中:

INSERT
    INTO
        Payments(Amount)
    VALUES(12.33)
WHERE
    Payments.CustomerID = '145300';

如何调整这个来执行？我也尝试过这样的事情:

IF NOT EXISTS(
    SELECT
        1
    FROM
        Payments
    WHERE
        Payments.CustomerID = '145300' 
) INSERT 
    INTO
        Payments(Amount)
    VALUES(12.33);

Answer 1

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

UPDATE Payments
SET Amount = 12.33
WHERE CustomerID = '145300'

否则，如果你想插入一个新行，那么你必须使用

IF NOT EXISTS(SELECT 1 FROM Payments WHERE CustomerID = '145300')
    INSERT INTO Payments(CustomerID,Amount)
    VALUES('145300',12.33)

或者，如果您想合并这两个命令(如果客户存在，请更新，否则插入新行)

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'