I have a tested update ... from.. statement, like so:
update distributors set lead_bonus = lead_bonus + a.sum_amt
from (
select target_member as id, sum(amount) as sum_amt
from bonus_gen_history
where bonus_type=2 and sub_type=1 and source_member in
(select id
from distributors
where (extract(epoch from (now()-reg_time))/60>363))
group by target_member
) a
where a.id=distributors.id
I'm having problem of constructing this statement from sqlalchemy:
pass_pending_period = session.query(Distributor.id).filter((func.now()-
Distributor.reg_time)/3600>72).subquery()
sum_amt = session.query(Bonus_gen_history.target_member,
func.sum(Bonus_gen_history.amount).label('sm')).\
filter(Bonus_gen_history.bonus_type==BonusType.sponsorBonus).\
filter(Bonus_gen_history.tran_type==TranType.addPd).\
filter(Bonus_gen_history.source_member.in_(pass_pending_period)).
group_by(Bonus_gen_history.target_member).subquery()
q = session.query(Distributor).update({Distributor.sponsor_bonus:
Distributor.sponsor_bonus+(sum_amt.c.sm)})
I keep getting the following error message:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM-
clause entry for table "anon_1"
LINE 1: ...s SET sponsor_bonus=(distributors.sponsor_bonus +
anon_1.sm)
^
'UPDATE distributors SET sponsor_bonus=(distributors.sponsor_bonus +
anon_1.sm)' {}
And I can't figure out how to add the "FROM-clause" in the above
query.
Can somebody help me out?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
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.
