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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
