最近一段时间经常会处理一些数据库表格，下面是一些常用的数据库处理语句 表更新 update alibaba inner join alibaba_tmp on alibaba.memberid=alibaba_tmp.memberid set alibaba.is_cxt=alibaba_tmp.is_cxt;
update alibaba inner join alibaba_tmp on alibaba.memberid=alibaba_tmp.memberid set alibaba.is_cxt=alibaba_tmp.is_cxt;
update alibaba inner join alibaba_tmp on alibaba.memberid=alibaba_tmp.memberid set alibaba.company_url=alibaba_tmp.company_url;
update alibaba_two inner join alibaba_tmp on alibaba_two.memberid=alibaba_tmp.memberid set alibaba_two.is_cxt=alibaba_tmp.is_cxt;
update alibaba_two inner join alibaba_tmp on alibaba_two.memberid=alibaba_tmp.memberid set alibaba_two.company_url=alibaba_tmp.company_url;
临时多表去重复 delete from alibaba_tmp where memberid in (select memberid from alibaba) OR memberid in (select memberid from alibaba_two) OR memberid in (select memberid from alibaba_nophone)
临时表去重复 DELETE FROM alibaba_tmp WHERE memberid IN (SELECT * FROM (SELECT memberid FROM alibaba_tmp GROUP BY memberid HAVING COUNT(memberid) > 1) t1) AND id NOT IN (SELECT * FROM (SELECT MIN(id) FROM alibaba_tmp GROUP BY memberid HAVING COUNT(memberid) > 1) t2)
删除无效数据 insert into alibaba_delete(company_name,company_url,memberid,is_cxt,contactor,phone,mobile,fax,address,product,business_scope,business_model,legal_person,reg_no,reg_date,company_type,update_on,remark) SELECT company_name,company_url,memberid,is_cxt,contactor,phone,mobile,fax,address,product,business_scope,business_model,legal_person,reg_no,reg_date,company_type,update_on,remark FROM alibaba where remark='nophone' OR remark='delete';
DELETE FROM alibaba WHERE remark='nophone' OR remark='delete'
新表去重复 DELETE FROM alibaba_new WHERE memberid IN (SELECT * FROM (SELECT memberid FROM alibaba_new GROUP BY memberid HAVING COUNT(memberid) > 1) t1) AND id NOT IN (SELECT * FROM (SELECT MIN(id) FROM alibaba_new GROUP BY memberid HAVING COUNT(memberid) > 1) t2)
注意：本文归作者所有，未经作者允许，不得转载