I'm trying to recreate the following SQL in SQLAlchemy:
update ignore abms_biog a join (select a.id, u.id as user_id from abms_biog a join 3md.users u on u.firstname = a.firstname and u.lastname = a.lastname and 3md.non_conflict_middlename(u.middlename, a.middlename)=1 and u.credentials in (4,5) and a.name_suffix = replace(u.namesuffix, '.', '') where a.user_id is null and a.name_suffix <> '' group by a.id having count(*) = 1) m on m.id = a.id set a.user_id = m.user_id As you can see it's currently done in raw SQL with a subquery. I've got the join and filter conditions down with: base_match = sess.query(md_users.id, abms_biog.id).\ filter(md_users.firstname == abms_biog.firstname).\ filter(md_users.lastname == abms_biog.lastname).\ filter(func.non_conflict_middlename(md_users.middlename, abms_biog. middlename) == 1).\ filter(md_users.credentials.in_([4,5])).\ filter(abms_biog.user_id == None).\ filter(abms_biog.name_suffix == func.replace(md_users.namesuffix,'.','')).\ filter(abms_biog.name_suffix != '').\ group_by(abms_biog.id).\ having(func.count() == 1) But I'm having trouble forming an update statement on this query that SQLAlchemy accepts. It doesn't seem to allow updates on GROUP BY's, and I've tried creating a subquery as well to no avail. I've tried the obvious update(abms_biog). \where(abms_biog.user_id == abms_ids).\ values(user_id = md_users.id) and a new select to isolate the column I'm updating select(base_match). with_only_columns([abms_biog.user_id]) This doesn't seem to be that unusual/complex of an operation so I'm assuming that I'm missing something obvious. Any help would be much appreciated! -- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
