表hr_carddata主键是YYMMDD, CardID, CardTime, CardMachineID 4个列组成的组合主键,
表hr_employee主要列是empno(主键),cardid
现在要将hr_carddata中的cardid更新为empno,
更新前查询
select YYMMDD, CardID, CardTime, CardMachineID,count(yymmdd) from hr_carddata
GROUP BY YYMMDD, CardID, CardTime, CardMachineID having count(yymmdd)>1
无结果
得出结论,hr_carddata主键唯一,没有重复
更新hr_carddata
update hr_carddataset hr_carddata.cardid=hr_employee.empno from hr_employee where hr_carddata.cardid=hr_employee.cardid and hr_employee.cardid<>hr_employee.empno
提示主键重复,疑问?empn列唯一,(YYMMDD, CardID, CardTime, CardMachineID
)组合唯一,但更新却出现重复
解决办法:
先将组合主键去掉,然后更新,再用
select YYMMDD, CardID, CardTime, CardMachineID,count(yymmdd) from hr_carddata
GROUP BY YYMMDD, CardID, CardTime, CardMachineID having count(yymmdd)>1
查询,查出了重复值,然后删除重复,再将主键加上,即可
为什么出现这样的问题?如下数据
cardid empno
000001 000002
000002 000004
000005 000006
000007 000008
000009 000010
000011 000012
更新前都是唯一的,但将cardid更新为empno,时出现重复,(更新是一行一行更新)