sql - MySQL update query that uses aggregation subquery on the same table -

i have table named "tablea" contains "customer_id" , "name" fields. added new field "ref". want update table "ref" field set 1001 first item of customer_id=1, 1002 2nd item of customer_id=1, etc. , again 1001 first item of customer_id=2, etc.

of course, i've got that-was-to-be-expected mysql error message "you can't specify target table 'tablea' update in clause".

also tried using (select next_ref (select ... a_sub.customer_id = a.customer_id) a_sub), mysql says "a.customer_id" isn't found.

here's query:

 update tablea set a.ref = (     select coalesce(max(a_sub.ref) +1, 1001)     tablea a_sub     a_sub.`customer_id` = a.`customer_id` ) o.ref null; 

question is: can done in 1 query or have use temporary table? help!

yes. need use update/join:

update tablea join        (select a_sub.`customer_id`, coalesce(max(a_sub.ref) +1, 1001) newref         tablea a_sub         group a_sub.`customer_id`        ) a_sub        on a_sub.`customer_id` = a.`customer_id`   set a.ref = newref   a.ref null; 

edit:

if want auto-incremented id, suggest define 1 such. if have put 1 in, can do:

update tablea cross join        (select @rn := coalesce(max(a_sub.ref) + 1, 1001) maxref         tablea a_sub        ) vars   set a.ref = (@rn := @rn + 1)   a.ref null;