Update Redshift table from query

Question

I'm trying to update a table in Redshift from query:

update mr_usage_au au
inner join(select mr.UserId,
                  date(mr.ActionDate) as ActionDate,
                  count(case when mr.EventId in (32) then mr.UserId end) as Moods,
                  count(case when mr.EventId in (33) then mr.UserId end) as Activities,
                  sum(case when mr.EventId in (10) then mr.Duration end) as Duration
           from   mr_session_log mr
           where  mr.EventTime >= current_date - interval '1 days' and mr.EventTime < current_date
           Group By mr.UserId,
                    date(mr.ActionDate)) slog on slog.UserId=au.UserId
                                             and slog.ActionDate=au.Date
set au.Moods = slog.Moods,
    au.Activities=slog.Activities,
    au.Durarion=slog.Duration

But I receive the following error:

ERROR: syntax error at or near "au".

Answer 1

This is completely invalid syntax for Redshift (or Postgres). Reminds me of SQL Server ...

Should work like this (at least on current Postgres):

UPDATE mr_usage_au
SET    Moods = slog.Moods
     , Activities = slog.Activities
     , Durarion = slog.Duration       
FROM (
   select UserId
        , ActionDate::date
        , count(CASE WHEN EventId = 32 THEN UserId END) AS Moods
        , count(CASE WHEN EventId = 33 THEN UserId END) AS Activities
        , sum(CASE WHEN EventId = 10 THEN Duration END) AS Duration
   FROM   mr_session_log
   WHERE  EventTime >= current_date - 1  -- just subtract integer from a date
   AND    EventTime <  current_date
   GROUP  BY UserId, ActionDate::date
   ) slog
WHERE slog.UserId = mr_usage_au.UserId
AND   slog.ActionDate = mr_usage_au.Date;

This is generally the case for Postgres and Redshift:

• Use a FROM clause to join in additional tables.
• You cannot table-qualify target columns in the SET clause.

Also, Redshift was forked from PostgreSQL 8.0.2, which is very long ago. Only some later updates to Postgres were applied.

• For instance, Postgres 8.0 did not allow a table alias in an UPDATE statement, yet - which is the reason behind the error you see.

I simplified some other details.