On Mar 12, 2011, at 11:41 PM, Darren Yin wrote:
> The MySQL documentation has the following example syntax for multi-table
> UPDATES:
>
> UPDATE items, month SET items.price=month.price
> WHERE items.id=month.id;
>
> Is there a way to generate that via SQLAlchemy? The closest thing to it I
> could find in the documentation was the correlated update, but that doesn't
> generate quite the same thing. Right now I'm just constructing the MySQL
> statement by hand and executing, but I'd like to do it through SQLAlchemy if
> I could.
MySQL's syntax is not standard SQL. The usual way to do this is to use a
correlated subquery:
UPDATE items set price=(select price from month where items.id=month.id)
SQLAlchemy:
items.update().values(price=select([month.c.price]).where(items.c.id==month.c.id).as_scalar())
ORM:
sess.query(Item).update(price=sess.query(Month.price).filter(Item.id==Month.id).as_scalar(),
False)
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
