We are creating following pipeline to see how upsert into JDBC Destination:

JDBC Lookup:

SQL query for SQL SERVER: 

SELECT CASE WHEN EXISTS ( SELECT * FROM dbo.freepets WHERE id = '${record:value('/id')}') THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END as test

SQL Query for Mysql: 

SELECT EXISTS(SELECT * FROM freepets WHERE id = '${record:value('/id')}') as test

Stream Selector Procesor:

Insert:

sdc.operation.type = 1 

Update:

sdc.operation.type = 3