CODEWITHSUNDEEP
postgresqlamazon-web-servicessql-updateamazon-redshift
user3600910
user3600910

Reputation: 3109

Update Redshift table from query

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".

Upvotes: 6

Views: 11534

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659207

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.

I simplified some other details.

Upvotes: 22

Related Questions