On Oct 13, 2014, at 2:03 PM, Ofir Herzas <herz...@gmail.com> wrote: > Thanks Michael but I'm trying to find a generic syntax that would work on > both mysql and Oracle and AFAIK mysql does not support window functions.
there might not be one in this case. I'd try stack overflow to see what you get from a SQL perspective. > > The result SQL in Oracle is: > UPDATE my_table SET "level"=anon_1.rank FROM (SELECT my_table.id AS id, CASE > my_table.employee_id WHEN @curType THEN @curRow := @curRow + 1 ELSE @curRow > := 1 AND @curType := my_table.employee_id END AS rank FROM my_table, (SELECT > @curRow:=0, @curType:=\'\') r ORDER BY my_table.employee_id, my_table.id) > anon_1 WHERE my_table.id = anon_1.id > > And the exception is "ORA-00933: SQL command not properly ended" > I'm unfamiliar with this syntax (update ... from ... where). Was this > properly constructed? > > > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On > Behalf Of Michael Bayer > Sent: Monday, October 13, 2014 8:53 PM > To: sqlalchemy@googlegroups.com > Subject: Re: [sqlalchemy] how to add an order column to an existing table > > in Oracle I'd update the table based on a SELECT from a window function: > > SELECT row number OVER (PARTITION BY employee_id ORDER BY id) FROM employee > > that should give you "1, 2, 3, ..." for each row within an employee id group > > > On Oct 13, 2014, at 1:44 PM, Ofir Herzas <herz...@gmail.com> wrote: > > > I have found a solution although it seems a bit messy and doesn't work on > Oracle (throws ORA-00933: SQL command not properly ended): > > my_table = sa.sql.table('my_table', > sa.Column('id', sa.BigInteger), > sa.Column('employee_id', sa.BigInteger), > sa.Column('level', sa.Integer) > ) > level = sa.case([(sa.text("@curType"), sa.text("@curRow := @curRow + > 1"))], > value=my_table.c.employee_id, > else_=sa.and_(sa.text("@curRow := 1"), sa.text("@curType > := my_table.employee_id"))).label("rank") > temp = sa.alias(sa.select([my_table.c.id, level]).\ > select_from(my_table).\ > select_from(sa.text("(SELECT @curRow:=0, @curType:='') > r")).\ > order_by(my_table.c.employee_id, my_table.c.id)) > query = my_table.update().values({my_table.c.level: > temp.c.rank}).where(my_table.c.id ==temp.c.id) > engine.execute(query) > > > Can someone help me out here? > I need to fix this query to work on both mysql and Oracle and if there is a > better way to achieve what I need, all the better ... > > > On Sunday, October 12, 2014 7:16:27 PM UTC+3, Ofir Herzas wrote: > I have the following table: > > my_table = sa.sql.table('my_table', > sa.Column('id', sa.BigInteger), > sa.Column('employee_id', sa.BigInteger) > ) > and I want to add a 'rank' column using alembic. The addition itself is > pretty straight forward using add_column but I also need to populate that > column using sqlalchemy based on existing information (each employee_id has > its own rank ordered by the id). > > For example, the following table: > > id employee_id rank > -- ----------- ---- > 1 1 1 > 2 1 2 > 5 1 3 > 3 2 1 > 4 3 1 > 6 3 2 > The problem is similar to this one. > > The only difference is that I need it working for sqlalchemy on top of both > mysql and oracle (a more generic solution is needed). > > Any advice? > > (BTW: I've posted the same question in SO here) > > > -- > 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 tosqlalchemy+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/d/optout. > > -- > You received this message because you are subscribed to a topic in the Google > Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/-rFuk112Q8I/unsubscribe. > To unsubscribe from this group and all its topics, 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/d/optout. > > -- > 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 tosqlalchemy+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/d/optout. -- 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/d/optout.
