sql - Updating a table with a groupwise maximum MYSQL? -
i had table duplicate user_ids many 1 relationship tasks , payment.
i used groupwise maximum on tasks return rows had highest task completion percentage. update new table highest payment completion stats same table.
for example:
id date taskid payid task_completion pay_value 4722 2007-11-08 16:20:14 2 3 7.14 0 4722 2007-11-08 16:20:14 3 3 0.00 0 4722 2007-11-08 16:20:14 5 3 0.00 0 4722 2007-11-08 16:20:14 2 6 7.14 40 4722 2007-11-08 16:20:14 3 6 0.00 40 4722 2007-11-08 16:20:14 5 6 0.00 40 4724 2007-11-20 15:32:40 4 7 25.71 105 4726 2008-01-28 11:44:50 7 8 7.14 52 4726 2008-01-28 11:44:50 8 8 34.29 52 4726 2008-01-28 11:44:50 10 8 65.71 52 4726 2008-01-28 11:44:50 7 9 7.14 24 4726 2008-01-28 11:44:50 8 9 34.29 24
so have used groupwise maximum on task completion create new table (with unique id) follows:
id date taskid payid task_completion pay_value 4722 2007-11-08 16:20:14 2 3 7.14 0 4724 2007-11-20 15:32:40 4 7 25.71 105 4726 2008-01-28 11:44:50 10 8 65.71 52
the problem id 4722 highest pay_value , payid have not been inserted resulting table.
i update in form (using select):
select s1.payid, s1.pay_value, f_tbl s1 left join f_tbl s2 on s1.cashid = s2.cashid , s1.pay_value > s2.pay_value s2.cash_id null group user_id;
however seems updating final table based on query not possible. stumped, thank assistance.
you need join
in update
this. assuming query correct:
update summarytable st cross join (select s1.payid, s1.pay_value, f_tbl s1 left join f_tbl s2 on s1.cashid = s2.cashid , s1.pay_value > s2.pay_value s2.cash_id null group user_id ) toupdate set st.payid = toupdate.payid, st.pay_value = toupdate.pay_value st.taskid = 4722;
Comments
Post a Comment