Data Target - Database

Example : Running a Merge Query in Oracle Database. 

Insert and Update at same time in ORDERS_TEST table

t is a alias for target table

s is a alias for source table

This will go inside the SQL Statment of Database Target

"""MERGE INTO ORDERS_TEST t
    USING (
        SELECT
            ? AS AMAZON_ORDER_ID,
            TO_DATE(?, 'YYYY-MM-DD') AS LAST_SHIP_DATE,
            ? AS ORDER_TYPE,
            TO_DATE(?, 'YYYY-MM-DD') AS PURCHASE_DATE,
            ? AS BUYER_EMAIL,
            TO_DATE(?, 'YYYY-MM-DD') AS AMZ_LAST_UPDATE_DATE,
            ? AS IS_REPLACEMENT_ORDER,
            ? AS NUM_ITEMS_SHIPPED,
            ? AS SHIPMENT_SERVICE_LEVEL,
            ? AS ORDER_STATUS,
            ? AS SALES_CHANNEL,
            ? AS IS_BUSINESS_ORDER,
            ? AS NUM_ITEMS_UNSHIPPED,
            ? AS GLOBAL_EXPRESS_ENABLED,
            ? AS IS_SOLDBY_AB,
            ? AS IS_PREMIUM_ORDER,
            ? AS ORDER_TOTAL_AMOUNT,
            ? AS ORDER_TOTAL_CURRENCY,
            TO_DATE(?, 'YYYY-MM-DD') AS EARLIEST_SHIP_DATE,
            ? AS MARKETPLACE_ID,
            ? AS FULFILLMENT_CHANNEL,
            ? AS PAYMENT_METHOD,
            ? AS SHIPPING_CITY,
            ? AS SHIPPING_POSTAL,
            ? AS SHIPPING_STATE,
            ? AS SHIPPING_COUNTRY,
            ? AS IS_ISPU,
            ? AS IS_PRIME,
            ? AS SELLER_ORDER_ID,
            ? AS SHIPMENT_SERVICE_CATEGORY,
            ? AS NEXTTOKEN
        FROM dual
    ) s
    ON (t.AMAZON_ORDER_ID = s.AMAZON_ORDER_ID)
    WHEN MATCHED THEN
        UPDATE SET
            t.LAST_SHIP_DATE = s.LAST_SHIP_DATE,
            t.ORDER_TYPE = s.ORDER_TYPE,
            t.PURCHASE_DATE = s.PURCHASE_DATE,
            t.BUYER_EMAIL = s.BUYER_EMAIL,
            t.AMZ_LAST_UPDATE_DATE = s.AMZ_LAST_UPDATE_DATE,
            t.IS_REPLACEMENT_ORDER = s.IS_REPLACEMENT_ORDER,
            t.NUM_ITEMS_SHIPPED = s.NUM_ITEMS_SHIPPED,
            t.SHIPMENT_SERVICE_LEVEL = s.SHIPMENT_SERVICE_LEVEL,
            t.ORDER_STATUS = s.ORDER_STATUS,
            t.SALES_CHANNEL = s.SALES_CHANNEL,
            t.IS_BUSINESS_ORDER = s.IS_BUSINESS_ORDER,
            t.NUM_ITEMS_UNSHIPPED = s.NUM_ITEMS_UNSHIPPED,
            t.GLOBAL_EXPRESS_ENABLED = s.GLOBAL_EXPRESS_ENABLED,
            t.IS_SOLDBY_AB = s.IS_SOLDBY_AB,
            t.IS_PREMIUM_ORDER = s.IS_PREMIUM_ORDER,
            t.ORDER_TOTAL_AMOUNT = s.ORDER_TOTAL_AMOUNT,
            t.ORDER_TOTAL_CURRENCY = s.ORDER_TOTAL_CURRENCY,
            t.EARLIEST_SHIP_DATE = s.EARLIEST_SHIP_DATE,
            t.MARKETPLACE_ID = s.MARKETPLACE_ID,
            t.FULFILLMENT_CHANNEL = s.FULFILLMENT_CHANNEL,
            t.PAYMENT_METHOD = s.PAYMENT_METHOD,
            t.SHIPPING_CITY = s.SHIPPING_CITY,
            t.SHIPPING_POSTAL = s.SHIPPING_POSTAL,
            t.SHIPPING_STATE = s.SHIPPING_STATE,
            t.SHIPPING_COUNTRY = s.SHIPPING_COUNTRY,
            t.IS_ISPU = s.IS_ISPU,
            t.IS_PRIME = s.IS_PRIME,
            t.SELLER_ORDER_ID = s.SELLER_ORDER_ID,
            t.SHIPMENT_SERVICE_CATEGORY = s.SHIPMENT_SERVICE_CATEGORY,
            t.NEXTTOKEN = s.NEXTTOKEN
    WHEN NOT MATCHED THEN
        INSERT (
            AMAZON_ORDER_ID,
            LAST_SHIP_DATE,
            ORDER_TYPE,
            PURCHASE_DATE,
            BUYER_EMAIL,
            AMZ_LAST_UPDATE_DATE,
            IS_REPLACEMENT_ORDER,
            NUM_ITEMS_SHIPPED,
            SHIPMENT_SERVICE_LEVEL,
            ORDER_STATUS,
            SALES_CHANNEL,
            IS_BUSINESS_ORDER,
            NUM_ITEMS_UNSHIPPED,
            GLOBAL_EXPRESS_ENABLED,
            IS_SOLDBY_AB,
            IS_PREMIUM_ORDER,
            ORDER_TOTAL_AMOUNT,
            ORDER_TOTAL_CURRENCY,
            EARLIEST_SHIP_DATE,
            MARKETPLACE_ID,
            FULFILLMENT_CHANNEL,
            PAYMENT_METHOD,
            SHIPPING_CITY,
            SHIPPING_POSTAL,
            SHIPPING_STATE,
            SHIPPING_COUNTRY,
            IS_ISPU,
            IS_PRIME,
            SELLER_ORDER_ID,
            SHIPMENT_SERVICE_CATEGORY,
            NEXTTOKEN
        ) VALUES (
            s.AMAZON_ORDER_ID,
            s.LAST_SHIP_DATE,
            s.ORDER_TYPE,
            s.PURCHASE_DATE,
            s.BUYER_EMAIL,
            s.AMZ_LAST_UPDATE_DATE,
            s.IS_REPLACEMENT_ORDER,
            s.NUM_ITEMS_SHIPPED,
            s.SHIPMENT_SERVICE_LEVEL,
            s.ORDER_STATUS,
            s.SALES_CHANNEL,
            s.IS_BUSINESS_ORDER,
            s.NUM_ITEMS_UNSHIPPED,
            s.GLOBAL_EXPRESS_ENABLED,
            s.IS_SOLDBY_AB,
            s.IS_PREMIUM_ORDER,
            s.ORDER_TOTAL_AMOUNT,
            s.ORDER_TOTAL_CURRENCY,
            s.EARLIEST_SHIP_DATE,
            s.MARKETPLACE_ID,
            s.FULFILLMENT_CHANNEL,
            s.PAYMENT_METHOD,
            s.SHIPPING_CITY,
            s.SHIPPING_POSTAL,
            s.SHIPPING_STATE,
            s.SHIPPING_COUNTRY,
            s.IS_ISPU,
            s.IS_PRIME,
            s.SELLER_ORDER_ID,
            s.SHIPMENT_SERVICE_CATEGORY,
            s.NEXTTOKEN
        )
    """