带有 WHERE 子句的 SQL Server INSERT INTO

2022-01-09 00:00:00 sql database insert tsql sql-server

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

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'

相关文章