On Oct 18, 2007, at 9:23 AM, Karl Pflästerer wrote:
>
> Hi,
> am I wrong, or is it at the moment not possible to write an update
> statement
> for Mysql which includes multiple tables like that:
>
> import sqlalchemy.sql as sql
>
> query = sql.text("""UPDATE shows AS s INNER JOIN show_artendef
> AS sad
> ON sad.id = s.art
> SET s.seq = s.seq + 1
> WHERE s.seq >= :newseq AND s.seq < :oldseq
> AND sad.name = :name""")
>
> At the moment I use the above but maybe I could write that without
> text?
> visit_update in mysql.py looks like at the moment I can't use
> multiple tables if I'm
> not wrong (but I didn't look too deep in the source code). I use SA
> 0.4.0
> KP
usually I do these as a correlated UPDATE. pretty sure mysql can do
these in version 5....
UPDATE shows set s.seq = s.seq+1
WHERE s.seq >= :newseq AND s.seq < :oldseq AND EXISTS
(select 1 from show_artendef AS sad where sad.name=:name and
sad.id=s.art)
SA looks like: shows.update(shows.seq.between(x, y) & exists([1],
and_(show_artendef.c.name='somename',
show_artendef.c.id==shows.c.id)), values={shows.c.seq:shows.c.seq+1})
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
