Hey,
I have a simple SQLAlchemy (0.8.4) insert command (with a subquery which
refers to the same table). Basically I want to use the max value of a column
and use that in an insert.
With MySQL this fails:
(1093, "You can't specify target table 'bar' for update in FROM clause")
'INSERT INTO bar (position) VALUES ((SELECT max(bar.position) AS max_1 \nFROM
bar))'
The problem is well explained in a StackOverflow answer [1]: I need to use an
alias for the "bar" table in the SELECT sub query. Instead of
(SELECT max(bar.position) AS max_1 \nFROM bar)
SQLAlchemy should emit something like
(SELECT max(foo.position) FROM bar as foo)
I found a very old thread (2007) which refers to "correlate" but Mike stated
that r2515 should have auto-correlation (and manually adding the call does not
change anything).
It looks to me as if SQLAlchemy adds a unnecessary alias for the "max"
expression but does not do so for the actual table name.
My reproduction script is at the end of this email. Is this is a missing
feature in SQLAlchemy or just some lack of knowledge on my part?
fs
[1] http://stackoverflow.com/a/14302701/138526
#!/usr/bin/env python
from sqlalchemy import create_engine, func, sql
from sqlalchemy.schema import Column, MetaData, Table
from sqlalchemy.types import Integer
metadata = MetaData()
bar = Table('bar', metadata,
Column('id', Integer, primary_key=True, autoincrement=True),
Column('position', Integer),
)
engine = create_engine('mysql://...:...@localhost/foo')
metadata.bind = engine
metadata.create_all()
connection = engine.connect()
connection.execute(
bar.insert().\
values({
'position': sql.select([func.max(bar.c.position)])
})
)
--
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/groups/opt_out.
