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 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
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.
