Hi, How can I reference a table that I am deleting from in an inner/nested statement?
Here is my simplified oritinal version, no inner condition: rownum = sql.expression.over(sql.func.row_number(), partition_by=table.c.some_id, order_by=table.c.dw_created_date.desc()) rownum = rownum.label("rownum") condition = table.c.dw_valid_to_date == None primary = sql.expression.select([table.c.dw_some_key, rownum]) primary = primary.where(condition).alias("primary") condition = primary.c.rownum == 1 unique = sql.expression.select([primary.c.dw_some_key]) unique = unique.where(condition).alias("unique") condition = table.c.dw_some_key.notin_(unique) delete = sql.expression.delete(table, whereclause=condition) Which produces something like the following (manually formatted + added comments about my intention): DELETE FROM foo a WHERE a.dw_key NOT IN ( SELECT "primary".dw_key FROM ( SELECT foo.dw_key AS dw_key, row_number() OVER (PARTITION BY foo.some_id ORDER BY foo.dw_created_date DESC) AS rownum FROM foo WHERE foo.dw_valid_to_date IS NULL -- Need to add this: -- AND a.some_id = foo.some_id ) AS "primary" WHERE "primary".rownum = 1 ) I want to add the condition commented-out in the above statement. My attempt was to alias the table: Aliased version with inner condition: inner = table.alias("inner") rownum = sql.expression.over(sql.func.row_number(), partition_by=inner.c.some_id, order_by=inner.c.dw_created_date.desc()) rownum = rownum.label("rownum") condition = inner.c.dw_valid_to_date == None condition = and_(inner.c.dw_valid_to_date == None, inner.c.some_id == table.c.some_id) primary = sql.expression.select([inner.c.dw_some_key, rownum]) primary = primary.where(condition).alias("primary") # Select unique and filter non-unique # condition = primary.c.rownum == 1 unique = sql.expression.select([primary.c.dw_some_key]) unique = unique.where(condition).alias("unique") condition = table.c.dw_some_key.notin_(unique) delete = sql.expression.delete(table, whereclause=condition) However this produces a product of the inner statement and the original table: DELETE FROM footable WHERE footable.dw_some_key NOT IN ( SELECT "primary".dw_some_key FROM ( SELECT "inner".dw_some_key AS dw_some_key, row_number() OVER (PARTITION BY "inner".some_id ORDER BY "inner".dw_created_date DESC) AS rownum # # Here is the problem introduced by the alias: # FROM footable AS "inner", footable WHERE "inner".dw_valid_to_date IS NULL AND "inner".some_id = footable.some_id) AS "primary" WHERE "primary".rownum = 1 You can see that the inner condition is not referencing the table used in the DELETE statement but a new reference to the table. How can I construct a statement where I can reference the tables as I need to in my case? Thanks for any hints, Stefan -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.