SQL Server插入WHERE子句。

I'm trying to insert some mock payment info into a dev database with this query:

我正在尝试将一些模拟支付信息插入到一个dev数据库中，并使用该查询:

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

5 个解决方案

#1

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

我认为您正在尝试做一个update语句(对于ID = 145300的客户，设置amount = 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)

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

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'

#2

If you want to insert new rows with the given CustomerID

如果您想用给定的id插入新的行

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

#3

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。在这种情况下，您应该使用update语句来更新一行。Insert语句将添加一个不能包含值的全新行。

#4

Do you want to perform update;

是否需要执行更新;

update Payments set Amount  = 12.33 where Payments.CustomerID = '145300'

#5

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的付款表进行插入，因为目前没有使用该CustomerID的付款，所以我不能更新它。

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、金额、PaymentTypeID) value ('145300'， 24.99, 8);然后从支付中选择*。CustomerID = ' 145300 ';确认一下，我们是做生意的。谢谢大家!

#1