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
set au.Moods = slog.Moods,
But I receive the following error:
12 ERROR: syntax error at or near "au".
This is completely invalid syntax for Redshift (or Postgres). Reminds me of SQL Server …
Should work like this (at least on current Postgres):
SET Moods = slog.Moods
, Activities = slog.Activities
, Durarion = slog.Duration
, 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
WHERE EventTime >= current_date - 1 -- just subtract integer from a date
AND EventTime < current_date
GROUP BY UserId, ActionDate::date
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
FROMclause to join in additional tables.
- You cannot table-qualify target columns in the
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
UPDATEstatement, yet – which is the reason behind the error you see.
I simplified some other details.