jeudi 23 janvier 2020

How to delete row after update in IF/ELSE statement?

My scenario -> I need to update record if exists and delete old one(I'm sure "old one exist") after update, else insert "old one". I cannot run DELETE querry after IF EXISTS since it may happen than old one will be needed (will be inserted from ELSE statement).

Here is my attempt :

IF EXISTS (SELECT * FROM dbo.JDOMINO_CON WHERE GBC = '72227')  
   UPDATE dbo.JDOMINO_CON 
   SET GBC = '72227', 
       Description = 'RES_TF,10k,0402,1%,0,1W,100PPM/C', 
       Buildneed = Buildneed + 200, 
       Replaced = '72223' 
   WHERE GBC = '72227'

DELETE FROM dbo.JDOMINO_CON 
WHERE GBC = '72223' /* If updated I need to delete replaced row */

ELSE /*Here is error : Incorrect syntax near 'ELSE'*/
    INSERT INTO dbo.JDOMINO_CON (GBC,Description, Buildneed, Replaced) 
    VALUES ('72227', 'RES_TF,10k,0402,1%,0,1W,100PPM/C', 200, NULL)

Sample data :

CREATE TABLE [dbo].[JDOMINO_CON] 
(
    [GBC]         INT          NULL,
    [Description] VARCHAR (80) NULL,
    [Buildneed]   INT          NULL,
    [Replaced]    VARCHAR (80) NULL
);

INSERT INTO @JDOMINO_CON (GBC, [Description], Buildneed, Replaced)
SELECT 72227, 'RES_TF,10k,0402,1%,0,1W,100PPM/C', 200, NULL
UNION ALL
SELECT 72223, 'RES_TF,10k,0402,5%,0,1W,100PPM/C', 200, NULL
UNION ALL

Expected output --> only 1 row --> 72227 /'RES_TF,10k,0402,1%,0,1W,100PPM/C'/ Buildneed = 400 / Replaced = 72223

So my question is: how can I add delete statement if record exists?

Aucun commentaire:

Enregistrer un commentaire