Refresh Redshift table from query

Question

I am trying to update a table in Redshift from a 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 get the following error:

ERROR: syntax error at or near "au".

Answer 1

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

It should work like this (at least in the 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 usually applies to Postgres and Redshift:

• Use the sentence FROMto join additional tables.
• You cannot table target columns in a sentence SET.

, Redshift PostgreSQL 8.0.2, . Postgres.

• , Postgres 8.0 UPDATE, - , .

.