mysql - (SQL) How can I put the count() into a column that already exists? -

in next code show alias count() "coin" want put column named "matches" in results, hope can me please.

the new column "matches' exists in table "candidates" , want fill count() values

sql code:

select table1.* , count(*) coin ( (select c.* jobweb.candidates c, jobweb.additional_knowledge ak (ak.candidate_id = c.candidate_id) , (ak.knowledge '%ccna%' or  ak.knowledge_description '%ccna%' or  ak.knowledge '%java%' or ak.knowledge_description '%java%')) union (select c.* jobweb.candidates c , jobweb.work_experience  ( we.candidate_id = c.candidate_id ) , ( we.position_name '%sdh%' or we.functions_desciption '%sdh%' or  we.position_name '%sharepoint%' or we.functions_desciption '%sharepoint%' or  we.position_name '%proyecto%' or we.functions_desciption '%proyecto%' or  we.position_name '%ingeniero%' or we.functions_desciption '%ingeniero%' )) union (select c.* jobweb.candidates c, jobweb.formal_education fe (fe.candidate_id =  c.candidate_id , fe.education_description '%ingeniero%')) )  table1 group table1.candidate_id order coin desc 

solution: discard use sql extract values on column "matches" used hibernate that:

public list<candidate> getcandidatesmatchesnativesql(string customquery) {     query query = sessionfactory.getcurrentsession().createsqlquery(customquery)             .addentity(candidate.class)             .addscalar("matchcounter");     @suppresswarnings("unchecked")     list<object[]> objects = query.list();     list<candidate> candidates = new arraylist<candidate>();     (object[] object : objects ) {         candidate candidate = (candidate) object[0];         biginteger match = (biginteger) object[1];         candidate.setmatches( match.intvalue() );         candidates.add(candidate);     }     return candidates; } 

just changed coin matches in select , order by.

       create temporary table if not exists table2     (select table1.candidate_id , count(*) coin   (     (select c.* jobweb.candidates c, jobweb.additional_knowledge ak     (ak.candidate_id = c.candidate_id) , (ak.knowledge '%ccna%' or      ak.knowledge_description '%ccna%' or      ak.knowledge '%java%' or ak.knowledge_description '%java%'))     union     (select c.* jobweb.candidates c , jobweb.work_experience      ( we.candidate_id = c.candidate_id ) ,     ( we.position_name '%sdh%' or we.functions_desciption '%sdh%' or      we.position_name '%sharepoint%' or we.functions_desciption '%sharepoint%' or      we.position_name '%proyecto%' or we.functions_desciption '%proyecto%' or      we.position_name '%ingeniero%' or we.functions_desciption '%ingeniero%' ))     union     (select c.* jobweb.candidates c, jobweb.formal_education fe     (fe.candidate_id =  c.candidate_id , fe.education_description '%ingeniero%'))     )  table1 group table1.candidate_id order coin desc)      update set a.matches=b.coin      candidates inner join table2 b on a.candidate_id=b.candidate_id