Thanks,
I used exists (reversed condition):
condition = primary.c.rownum != 1
unique = sql.expression.exists([1],
whereclause=condition)
delete = sql.expression.delete(table,
whereclause=unique)
I'm still getting: "FROM footable AS "inner", footable" for the inner-most
statement. Note that I have deeper nesting – the "primary" is already
nested, because I can't use window functions in the WHERE clause.
Note that I need to have the inner.key = to_be_deleted.key condition in the
inner-most SELECT, not in the EXISTS portion. See my 'condition' for
'promary' in my example above (there is one stray line, that should be
ignored).
Or how do you meant to use the the exists() construct?
Stefan
On Wednesday, December 3, 2014 2:05:32 PM UTC-5, Michael Bayer wrote:
>
> 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....@gmail.com
> <javascript:>> 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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
