Tukukkk

I'm attempting to update an is_latest column on a redshift table grouped by source and source_primary_key with the following update statement, but getting an error that window functions are not allowed in update statements. What is the best way to go about this?

update my_schema.production_log

set is_latest = 

case when run_start_time = max(run_start_time) over (partition by 

source,   source_primary_key)

then ‘t’ else ‘f’

end

Redshift supports a FROM clause in the update statement. Try this:

update my_schema.production_log

    set is_latest = (case when run_start_time = max_run_start_time

                          then 't' else 'f'

                     end)

    from (select source, source_primary_key, max(run_start_time) as max_run_start_time

          from my_schema.production_log

          group by source, source_primary_key

         ) pl2

    where pl2.source = production_log.source and

          pl2.source_primary_key = production_log.source_primary_key;

You need to rewrite the query so it can join to a derived table:

UPDATE u

SET u.is_latest =CASE

                     WHEN u.run_start_time = j.max_time THEN 't'

                     ELSE 'f'

                 END

FROM my_schema.production_log AS u

INNER JOIN (

    SELECT

        source

      , source_primary_key

      , MAX( run_start_time ) max_time

    FROM my_schema.production_log

    GROUP BY

        source

      , source_primary_key

) AS j

    ON u.source = j.source

    AND u.source_primary_key = j.source_primary_key