On Mon, Jan 15, 2018 at 6:09 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > On Mon, Jan 15, 2018 at 5:32 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > that's not quite right, because I'm not selecting from Test. Adding > the JOIN back in, I've tried aliasing Test everywhere and it still > produces the error. MySQL is really bad at subqueries. give me a > minute to find a query that works here.
OK try this: latest_items = session.query( Test.value, func.max(Test.timestamp).label('latest_timestamp') ).group_by(Test.value).subquery(name='subquery1') deleted_rows = ( session.query(Test) .filter((Test.timestamp < 5)) .filter( ~exists(). where(Test.value == latest_items.c.value). where(Test.timestamp == latest_items.c.latest_timestamp) ).delete(synchronize_session=False) ) output: DELETE FROM test WHERE test.timestamp < %s AND NOT (EXISTS (SELECT * FROM (SELECT test.value AS value, max(test.timestamp) AS latest_timestamp FROM test GROUP BY test.value) AS subquery1 WHERE test.value = subquery1.value AND test.timestamp = subquery1.latest_timestamp)) > > > > > >> >> >> >> >>> >>> … I guess that's a bug? At least I would assume that this should not result >>> in an OperationalError, especially since it works with sqlite? >>> >>> Thanks for any help, >>> >>> Lukas >>> >>> [0] https://tinloaf.de/~tinloaf/sqlalchemy/mariadb.html >>> [1] https://tinloaf.de/~tinloaf/sqlalchemy/sqlite.html >>> [2] >>> https://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> 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 https://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.