Thanks a lot! So, if I get this correctly, the difference is that you assigned a label to the timestamp, and that you're doing "~exists()" instead of "~in()" in the delete clause, right?
I'll have to adapt this a bit to get it into my (obviously a bit more complicated) actual code, but thanks for the help so far! Lukas On Tuesday, January 16, 2018 at 12:14:14 AM UTC+1, Mike Bayer wrote: > > On Mon, Jan 15, 2018 at 6:09 PM, Mike Bayer <[email protected] > <javascript:>> wrote: > > On Mon, Jan 15, 2018 at 5:32 PM, Mike Bayer <[email protected] > <javascript:>> 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 [email protected] <javascript:>. > >>> To post to this group, send email to [email protected] > <javascript:>. > >>> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
