On Feb 28, 2014, at 11:20 AM, Felix Schwarz <felix.schw...@oss.schwarz.eu> wrote:
> 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)
first off, there’s nothing wrong with the SQL in general, the statement as is
works in Postgresql, SQLite.
So MySQL doesn’t like “bar” stated twice. easy enough just use an alias():
connection.execute(
bar.insert().\
values({
'position': sql.select([func.max(bar.alias().c.position)])
})
)
output passes:
INSERT INTO bar (position) VALUES ((SELECT max(bar_1.position) AS max_1
FROM bar AS bar_1))
> [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 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/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
