CODEWITHSUNDEEP
amazon-redshift
user3386284
user3386284

Reputation: 63

RedShift: Delete statement does not allow table alias?

Got the the following code to try to blow away duplicate entries in the table:

DELETE FROM events a
WHERE a.ctid <> (SELECT min(b.ctid)
             FROM   events b
             WHERE  a.eventid = b.eventid and
                    left(b.eventtimestamp,10)='2016-01-15');

Tried this with my query tool and with psql, and I get:

Error : ERROR:  syntax error at or near "a"

It is objecting to the alias. I've tried different names, I've tried inserting the "AS" key word, all to no avail. From what I can tell, Redshift supports table aliases. So what am I doing wrong?

Upvotes: 2

Views: 4977

Answers (2)

Joe Harris
Joe Harris

Reputation: 14035

Redshift doesn't know about ctid because you didn't alias min(b.ctid).

AFAICT, this delete is possible but not in the way you structured it. Your subquery is correlated with the deletion target. I (personally) would not feel confident about the right rows being deleted with that query structure, even it it would run.

Since Redshift does not support joins in DELETE I reworked it a bit to use an IN list. Innermost to outer it:

  • finds the MIN() ctid per eventid for the given date
  • joins to events again where ctid does not match
  • concatenates eventid with ctid
  • DELETE from events using an IN list and the same concat

The advantage here is that you can confirm exactly which combinations of eventid and ctid will be deleted from the table. It's not clear to me whether you would also need to limit the events to a given date in the outer subquery.

DELETE FROM events
WHERE eventid||ctid IN (SELECT a.eventid||a.ctid
                        FROM   events AS a  
                        JOIN   (SELECT eventid, MIN(b.ctid) ctid
                                FROM   events
                                WHERE  LEFT(b.eventtimestamp,10)='2016-01-15'
                                GROUP BY eventid) AS b
                        ON     a.eventid = b.eventid
                        AND    a.ctid <> b.ctid
;

Upvotes: 2

user3386284
user3386284

Reputation: 63

Looks like the answer is that an alias is not supported there- finally tracked down the latest doc and it does not show a table alias as an option for the delete.

Also, /BTW, when I dropped the alias, RS said it didn't know about ctid- which is in Postgres 8.0 and 8.2. So this probably won't work for RS anyway.

Upvotes: 1

Related Questions