SQL Server INSERT INTO with WHERE子句

我想你是想做一个更新声明 (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'

如果要插入具有给定CustomerID的新行

INSERT
    INTO
        Payments(Amount,CustomerID )
VALUES(12.33,'145300');

否则,如果您已经为客户付款,您可以执行以下操作:

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

听起来好像已经设置了customerID。在这种情况下,应该使用update语句来更新行。Insert语句将添加一个不能包含值的全新行。

是否要执行更新;

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

好的,看起来我实际上需要插入到具有正确CustomerID的付款表中,因为目前没有具有该CustomerID的付款,所以我无法更新它。

我跑了 INSERT INTO Payments (CustomerID, Amount, PaymentTypeID) Values ('145300', 24.99, 8); 然后 SELECT * FROM Payments WHERE Payments.CustomerID = '145300'; 确认一下,我们就开始做生意了。谢谢大家!

更好的解决方案,无死锁风险:

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

INSERT INTO Payments(CustomerID,Amount)
    SELECT '145300',12.33
WHERE @@ROWCOUNT=0

如果记录不存在,我会以这种方式插入到表中。可能不完全是在追求什么,但可能会有所帮助

insert into x (a,b)
select 1,2
where 0=(select count(*) from x where a = 1 and b = 2)

如果要修改现有行,请尝试如下更新,

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