Update statement with join for Mysql

[Karl Pflästerer]

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

[Nebur]

Joining tables in an UPDATE statement is not defined in any SQL
standard (please correct me if I'm not up to date). MySQL introduced
that with some 4.x version.
That's why I'd really be surprised to find support of SA for this
feature...I'm afraid you are right that this is not possible. But
there might be a way to rewrite it using a subquery. (?)
Ruben

[Michael Bayer]

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})