W.H W.H - 1 month ago 9
SQL Question

Conditional Update / Insert - Incorrect Syntax Near 'END'

I'm having some difficulty writing a query that updates or inserts, based on whether there is an existing entry.

The current query I'm running is:

IF EXISTS (SELECT 1 FROM dbContactEmails WHERE contID = 1)
BEGIN
UPDATE dbContactEmails
SET contEmail = 'test@test.com'
WHERE contID = 1
AND contCode = 'HOME'
END
ELSE
INSERT INTO dbContactEmails (contID, contEmail, contCode, contOrder, contActive)
VALUES (1, 'test@test.com', 'HOME', 0, 1)
END


Unfortunately this doesn't work, with the error "incorrect syntax near 'END'" being thrown.

Answer

Put BEGIN after ELSE

IF EXISTS (SELECT 1 FROM dbContactEmails WHERE contID = 1) 
BEGIN 
    UPDATE dbContactEmails 
    SET contEmail = 'test@test.com' 
    WHERE contID = 1 
    AND contCode = 'HOME' 
END 
ELSE 
BEGIN
    INSERT INTO dbContactEmails (contID, contEmail, contCode, contOrder, contActive) 
    VALUES (1, 'test@test.com', 'HOME', 0, 1) 
END