mysql主键记录存在更新某字段

1.以一个表的数据为基准向另外的表中插入数据，如果主键记录存在更新某字段

insert into role(player_id,hp,mp) select player_id,0,0 from player on duplicate key update hp=3; 

*update hp=3 表示如果是主键存在的情况下执行

INSERT INTO t_player(player_id,carrier_operator) SELECT a.player_id,b.pt from t_player a JOIN t_z_temp b ON (a.login_name = b.`name` OR a.guest_key = b.phone_id)  on DUPLICATE key UPDATE carrier_operator=b.pt; 

 

2.找出不存在的记录插入到表中

insert into role(player_id,hp,mp) select player_id,0,0 from player WHERE not exists (SELECT * from role where role.player_id=player.player_id);