How to INSERT or UPDATE depending on the result of SELECT in 1 statement

I'm trying to create an INSERT or UPDATE statement depending on the result of a SELECT statement in one go.

Currently, I have this statement that doesn't work:

UPDATE Inventory 
SET CQuantity = CQuantity + 1 
WHERE CBarcode = '00029218' 
  AND COALESCE(COUNT(SELECT CQuantity 
                     FROM Inventory 
                     WHERE CBarcode = '00029218'), 0) > 1;

Based on the SQL statement, it's supposed to update the quantity in the inventory table if the barcode is the specified value and if it finds an existing row in the inventory table. It doesn't work though.

I would like to UPDATE quantity if it finds an existing row in the inventory table, otherwise, it would INSERT a new row if barcode doesn't exist.

Thank you so much.

This is a standard UpSert. Your logic should look like this.

DECLARE @barcode varchar(30) = '00029218';

SET XACT_ABORT, NOCOUNT ON;
BEGIN TRAN;

UPDATE Inventory WITH (UPDLOCK, HOLDLOCK)
SET CQuantity += 1
WHERE CBarcode = @barcode;

IF @@ROWCOUNT = 0
    INSERT Inventory (CBarcode, CQuantity)
    VALUES (@barcode, 1);

COMMIT;

Take a read of this article, and note the following:

• You need a transaction to ensure consistency.

• You need to add UPDLOCK and HOLDLOCK/SERIALIZABLE hints to ensure correct locking.

• You don't need to check first using a SELECT, it's wasteful. Just do the UPDATE, and if the @@ROWCOUNT is 0 then do the INSERT.

• You can swap around the UPDATE and INSERT if the latter is more likely.

• Don't use MERGE in SQL Server if you have a choice, it's full of bugs.

Try to write maintainable and easily readable code. No one ever complained because they could understand it! For a slightly less readable but more optimized method see @Charlieface's answer.

SELECT @Exists = CQuantity FROM Inventory WHERE CBarcode = '00029218'

BEGIN
IF @Exists > 0 THEN
    UPDATE Inventory SET CQuantity = CQuantity + 1 WHERE CBarcode = '00029218'
ELSE
    INSERT INTO Inventory (CBarcode, CQuantity) VALUES ('00029218', 1)
END IF