The fix mentioned in the ticket works for me. Thanks, Dheeraj
On Friday, 28 June 2013 20:45:50 UTC+5:30, Michael Bayer wrote: > > > On Jun 28, 2013, at 10:26 AM, RedBaron <[email protected] <javascript:>> > wrote: > > Thanks for the reply. I understand that update against multiple tables is > non-standard. However, I think I am trying to update only one table > (events). > The equivalent of what I am trying to do is > update event set is_deleted=1 where (sid,cid) in (select > event.sid,event.cid from event join iphdr on .... where iphdr.ip_dst=XXX); > But MySQL-5.1.52 does not allow us to reference event in the in clause > > So I figured using a join would be a good idea. "Get the sid,cid of > relevant events and set their is_deleted to 1" > > > I agree that's the best query to use. But Event here doesn't have a > surrogate primary key column ? You could use that instead in your WHERE > criterion: WHERE event.id IN (select event.id FROM ...) > > > > > > > I can't hard-code params beacuse their are over 20 searchable parameters > and they can occur in almost any combination. Probably another way (long > and not good) is to fire a query and do a > for a in result: > a.isdel = True > > > > On Friday, 28 June 2013 19:44:29 UTC+5:30, Michael Bayer wrote: >> >> >> On Jun 28, 2013, at 8:23 AM, RedBaron <[email protected]> wrote: >> >> > But when I try to write it in SQLALchemy >> > >> > inner_q = >> session.queryEvent.sid.label('sid'),Event.cid.label('cid')).options(lazyload('*')).join(Event.iphdr).filter(IpHdr.ip_dst==func.inet_aton("192.168.2.10")).subquery() >> >> >> > update_stmt = >> tEvent.__table__.update().where(and_(inner_q_s.c.sid==Event.sid,inner_q_s.c.cid==Event.cid)).values({'is_deleted':True,}) >> >> >> > session.get_bind().execute(update_stmt) >> > >> > I get the correct statement but parameter order is wrong. From the >> debug >> > >> > 2013-06-28 17:49:53,999 INFO [sqlalchemy.engine.base.Engine][worker 4] >> > UPDATE event, (SELECT event.sid AS sid, event.cid AS cid FROM event >> LEFT OUTER JOIN iphdr ON event.sid = iphdr.sid AND event.cid = iphdr.cid >> WHERE event.is_deleted = false AND iphdr.ip_dst = inet_aton(%s)) AS >> anon_1 SET event.is_deleted=%s WHERE anon_1.sid = event.sid AND >> anon_1.cid = event.cid >> > >> > 2013-06-28 17:49:54,000 INFO sqlalchemy.engine.base.Engine (1, >> '192.168.2.10') >> > As can be seen the order is reversed to what should ideally be there. >> > >> > In general, the update value is always the first and then all the >> search parameters follow as per their order. >> > >> > Is this a bug or am I doing something wrong? >> >> I've created http://www.sqlalchemy.org/trac/ticket/2768 for this, and to >> understand what's going wrong one needs to appreciate that UPDATE.. against >> multiple tables is a non-standard syntax, where different backends put the >> second table in different places. this demo illustrates the issue: >> >> from sqlalchemy.sql import table, column, select >> from sqlalchemy.dialects import mysql >> >> t1 = table('t1', column('x')) >> t2 = table('t2', column('y'), column('z')) >> >> subq = select([t2]).where(t2.c.y == 7).alias() >> >> stmt = t1.update().values(x=5).where(t1.c.x == subq.c.z) >> >> compiled = stmt.compile(dialect=mysql.dialect()) >> >> # default impl, UPDATE..FROM . y follows x >> print stmt >> >> # mysql impl, UPDATE A, B, x follows y >> print compiled >> >> # but still getting y follows x >> print compiled.positiontup >> >> >> I don't have too great of a workaround here, in this case you can >> hardwire the IP number argument using literal_column: >> func.inet_aton(literal_column("'192.168.1.1'")) > > > -- > 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 http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
