how to insert or update if there is already a record?

Question

My table has two columns: Amount and Date

When I am adding new values, I want to update the amount if there is already a record with that date. If there is not, I want to just insert my new values.

Answer 1

something like:

if exists(select * from table where date = @date)
    update table set amount = @amount where date = @date
else
    insert table (amount, date) select @amount, date

Answer 2

Assuming you have at least SQL Server 2008, you could use the MERGE keyword. This keyword was added specifically for cases like yours. You specify a MERGE clause (basically a join) and then statements telling it how to handle when it does or doesn't find a match.

There's a decent example at the bottom that shows merging between a target table and a source "table" created out of a single row of parameters. To boil it down a bit, you could use something like:

MERGE [TargetTable] AS target -- This is the table we'll be updating
    USING (SELECT @Date) AS source (Date) -- These are the new values
    ON (target.Date = source.Date) -- Here we define how we find a "match"
WHEN MATCHED THEN  -- If the date already exists:
    UPDATE SET Amount = @Amount
WHEN NOT MATCHED THEN -- If the date does not already exist:
    INSERT (Date, Amount) VALUES (@Date, Amount)

Note that the nested UPDATE and INSERT clauses do not specify a table. That is because we already told SQL Server which table to perform those actions on when we defined our target table (the table specified right after the MERGE keyword).

Update: This functionality is apparently not supported by SQL Server CE, so it will not work in your specific case. I am leaving this answer here as it may help others attempting to do something similar in the full version of SQL Server. For the SQL Server CE solution, check out BJury's answer.

Answer 3

As an alternative to IF .. NOT EXISTS, you could assert an UPDATE, and fall back on an insert. This is usually quite a performant UPSERT pattern if data generally does exist already.

UPDATE MyTable
  SET Amount = @NewAmount
  WHERE Date = @Date;

IF @@ROWCOUNT = 0
  INSERT INTO MyTable(Amount, Date) VALUES (@NewAmount, @Date);

Answer 4

If you are using nhibernate ORM, then you can use SaveOrUpdate session method.