SQL Server INSERT INTO with WHERE clause

Question

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

Solution 1

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'

Solution 2

If you want to insert new rows with the given CustomerID

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

Solution 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.

Solution 4

Do you want to perform update;

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

View more solutions

Comments

[Matt Larsuma]

Ah. Then I can run select Amount from Payments where Payments.CustomerID = '145300'; to check that it was successful?

[TheOni]

@MattLarson Yes you can run that select to check the result

[MikeTeeVee]

I want to +1 this, but what if you later decide to only Update when the Amount is different. Do that (and run this script twice) and it will not Update a second time and @@RowCount will equal Zero (0), causing the Insert to fire and possibly break a Unique Key. It is best to use WHERE NOT EXISTS (SELECT * FROM Payments as P WHERE P.CustomerID = @CustomerID) as your Predicate.