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

Are you looking for an ["UPSERT"](https://stackoverflow.com/a/27545377/335858)? — Sergey Kalinichenko, Jan 04 '18 at 19:20
Please follow this link. Use both if and else [https://stackoverflow.com/a/11010548/5063562](https://stackoverflow.com/a/11010548/5063562) — Saikat Kundu Chowdhury, Jan 04 '18 at 19:22
I'm actually looking to add Payment info to a table that has a relationship with a Customer that exists. There are currently no payments. I was trying to add the amount, but I think I actually need to add several other properties like PaymentID, CreateDate, PaymentTypeID, etc. — Matt Larson, Jan 04 '18 at 19:28
Does this answer your question? [insert into values with where clause](https://stackoverflow.com/questions/9166157/insert-into-values-with-where-clause) — MikeTeeVee, Mar 04 '21 at 18:53

Hadi · Accepted Answer · 2018-01-04T19:27:08.417

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'

score 2 · Answer 2 · answered Jan 04 '18 at 19:19

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

answered Jan 04 '18 at 19:19

TheOni

715
8
25

Ah. Then I can run `select Amount from Payments where Payments.CustomerID = '145300'; ` to check that it was successful? – Matt Larson Jan 04 '18 at 19:24
@MattLarson Yes you can run that select to check the result – TheOni Jan 04 '18 at 19:36

score 1 · Answer 3 · answered Jan 04 '18 at 19:18

1

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.

answered Jan 04 '18 at 19:18

Frank Förster

341
1
4

score 1 · Answer 4 · answered Jan 04 '18 at 19:19

1

Do you want to perform update;

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

answered Jan 04 '18 at 19:19

lucky

11,548
4
18
35

Matt Larson · Answer 5 · 2018-01-04T19:39:05.320

0

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.

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!

edited Jan 04 '18 at 19:39

answered Jan 04 '18 at 19:33

Matt Larson

1,049
3
11
31

score 0 · Answer 6 · edited May 03 '19 at 15:23

0

Better solution and without risk of deadlocks:

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

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

edited May 03 '19 at 15:23

Pika the Master of the Whales

3,067
8
23
36

answered May 03 '19 at 15:22

Vitor Gouveia

1

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. – MikeTeeVee Mar 04 '21 at 22:03

score 0 · Answer 7 · answered Jan 10 '20 at 15:27

0

i do inserts into a table if the record doesn't exist this way. may not be entirely what is after but it may be helpful

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

answered Jan 10 '20 at 15:27

Ab Bennett

1,191
9
23

SQL Server INSERT INTO with WHERE clause

7 Answers7

Linked