Use SQL OUTPUT Clause Conditionally based on UPDATE result

22 views Asked by At

I am curious if the OUTPUT clause can be used conditionally based on the UPDATE that creates the insert.

For example, with a boolean checkbox...

UPDATE myTable1 SET BinCheckBox = CASE WHEN BinCheckBox = '1' THEN 1 ELSE 0 END

OUTPUT

inserted.col1
inserted.col2

INTO

myTable2

WHERE myTable1.id = myFormField.id

...now, this part works fine. But I only want to use OUTPUT if the row in myTable1 is actually updated to myTable1.BinCheckBox = 1.

I had this working with a database Trigger, but I would like it all to happen in my statement if possible. Psuedo code like...

UPDATE myTable 1 SET BinCheckBox = CASE WHEN BinCheckBox = '1' THEN 1 ELSE 0 END...AND IF BinCheckBox updated to 1, then OUTPUT...

Thank you for any advice

UPDATE / ANSWER

So instead of using OUTPUT, I am using INSERT INTO myTable2 SELECT, and a DELETE myTable2 FROM ... , to handle removing rows. Like so...

UPDATE myTable1 SET BinCheckBox = CASE WHEN BinCheckBox = '1' THEN 1 ELSE 0 END

INSERT INTO myTable2 (col1,col2)
SELECT col1,col2
FROM myTable1 m1
WHERE (BinCheckBox = 1)
AND NOT EXISTS (SELECT m2.id FROM myTable2 m2
WHERE (m2.id=m1.id));

DELETE m2 FROM myTable2
JOIN myTable1 m1 on m2.id=m1.id
WHERE m1.id=,y.id AND m1.BinCheckBox = 0;

This seems to work great.

0

There are 0 answers