why don’t you use a NOT EXISTS correlated subquery? that way the subquery can
refer to the outer table completely, rather than having just one column where
you can call IN.
> On Dec 3, 2014, at 12:36 PM, Stefan Urbanek <stefan.urba...@gmail.com> wrote:
>
> 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.
--
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.
