On Jun 28, 2013, at 10:26 AM, RedBaron <dheeraj.gup...@gmail.com> 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 <dheeraj...@gmail.com> 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 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/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 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/groups/opt_out.
