Execute Query depending upon select query value

Question

How to achieve the following query -

SELECT 
   CASE 
      WHEN COUNT(*) > 0 THEN 
         -- UPDATE operation
      ELSE 
         -- INSERT operation
   END AS [RESULT] 
FROM 
   [TABLE_1] 
WHERE 
   [CONDITION]

I am using SQL Server 2000. Can someone please help me?

Answer 1

Use EXISTS not COUNT as per my answer

IF EXISTS (SELECT * FROM [TABLE_1])
    UPDATE [TABLE_1] SET X = Y WHERE [CONDITION]
ELSE 
    INSERT INTO [TABLE_1] (X) VALUES (Y)

If you expect a very high proportion of updates, then why touch the table twice?

UPDATE [TABLE_1] SET X = Y WHERE [CONDITION]
IF @@ROWCOUNT = 0 
    INSERT INTO [TABLE_1] (X) VALUES (Y)

You can't do the same for a very high proportion of INSERTs on SQL Server 2000: you need TRY/CATCH to handle duplicates as per my answer

Answer 2

By far the easiest way to do this is to have two separate queries, something like:

IF (SELECT COUNT(*) FROM [TABLE_1]) > 0
    UPDATE [TABLE_1] SET X = Y WHERE [CONDITION]
ELSE 
    INSERT INTO [TABLE_1] (X) VALUES (Y)