SQL Select: Update if exists, Insert if not - With date part comparison?

Question

I need to update a record in a database with the following fields

[ID] int (AutoIncr. PK)
[ScorerID] int
[Score] int
[DateCreated] smalldatetime

If a record exists for todays date (only the date portion should be checked, not the time) and a given scorer, I'd like to update the score value for this guy and this day. If the scorer doesn't have a record for today, I'd like to create a new one.

I'm getting grey hair trying to figure how to put this into a single (is this possible?) sql statement. By the way I'm using an MSSQl database and the ExecuteNonQuery() method to issue the query.

Answer 1

IF EXISTS (SELECT NULL FROM MyTable WHERE ScorerID = @Blah AND CONVERT(VARCHAR, DateCreated, 101) = CONVERT(VARCHAR, GETDATE(), 101))
    UPDATE MyTable SET blah blah blah
ELSE
    INSERT INTO MyTable blah blah blah

Answer 2

The other guys have covered 2005 (and prior) compatible T-SQL/apprroaches. I just wanted to add that if you are lucky enough to be working with SQL Server 2008, you could take advantage of the new Merge (sometimes referred to as Upsert) statement.

I had trouble finding a blog entry or article which explains it further, but I did find this rather (1) helpful entry. The official MSDN entry is (2) here.

(1) [http://www.sqlservercurry.com/2008/05/sql-server-2008-merge-statement.html]
(2) [http://msdn.microsoft.com/en-us/library/bb510625.aspx]