Question 204423 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Workaround for using additional predicates with WHEN MATCHED clause in Redshift

I have the following merge query executed in Snowflake that I’m trying to re-write for Redshift:


MERGE INTO dest USING src
ON
    
    src.post_code = dest.post_code
    AND src.location =  dest.location
    AND src.ad= dest.ad

WHEN MATCHED AND src.delivered_on >= dest.delivered_on
    AND src.accuracy > dest.accuracy
    THEN UPDATE SET
        speed = src.speed,
        manufacturer = src.manufacturer,
        device_model = src.device_model
WHEN NOT MATCHED THEN
    INSERT (
        delivered_on,
        ad,
        location,
        accuracy,
        speed,
        manufacturer,
        device_model,
        post_code
    ) VALUES (
        src.delivered_on,
        src.advertiser_id,
        src.location,
        src.accuracy,
        src.speed,
        src.manufacturer,
        src.device_model,
        src.post_code
    );

When I try to execute, I get an error that says Additional predicate in WHEN MATCHED clause is not supported.

Apparently, multiple predicates with the WHEN MATCHED clause is not supported. How do I re-write the query to workaround this limitation?

Tags:

2

Answers


  1. 0

    You can simulate your prior merge by using queries to determine what can be updated and then what can be inserted (instead of using merge) e.g:

    -- UPDATE matching rows
UPDATE dest
SET speed = src.speed, manufacturer = src.manufacturer, device_model = src.device_model
FROM src
WHERE src.post_code = dest.post_code
    AND src.location = dest.location
    AND src.ad = dest.ad
    AND src.delivered_on >= dest.delivered_on
    AND src.accuracy > dest.accuracy;

-- INSERT unmatched rows
INSERT INTO dest (delivered_on, ad, location, accuracy, speed, manufacturer, device_model, post_code)
SELECT src.delivered_on, src.advertiser_id AS ad, src.location, src.accuracy, src.speed, src.manufacturer, src.device_model, src.post_code
FROM src
LEFT JOIN dest ON src.post_code = dest.post_code
    AND src.location = dest.location
    AND src.ad = dest.ad
WHERE dest.post_code IS NULL;

    Or you could continue to use the merge for inserts if you want.

    Login or Signup to reply.
  2. 0

    Remember that MERGE in RS is only a macro. Under the hood it issues exactly the same SQL commands you would – UPDATE, INSERT, etc. Also note when both the MATCH and NOT MATCH never overlap, you can write code which is does a lot less work than MERGE.

    https://www.redshiftresearchproject.org/white_papers/downloads/MERGE.html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search