UPDATE Table1, Table2
SET Table1.Col1 = 1, Table1.Col2 = 2, Table2.Col1 = 3
WHERE Table1.PKey = Table2.PKey AND Table1.PKey = 199
results in duplicate key entry
导致重复键输入
#1062 - Duplicate entry '199-1' for key 'PRIMARY'
please help i've been scratching my head. I want this query to be optimal and fast since its used inside a loop that loops through a lot of rows and updates for each one. Dont want to split into 2 queries since then it will be 2 separate query calls per row.
请帮助我一直在挠头。我希望这个查询是最佳和快速的,因为它在循环中使用,循环遍历许多行并为每个查询更新。不想分成2个查询,从那时起每行将有2个单独的查询调用。
2 个解决方案
#1
1
Your query above should work -- reference:
您的上述查询应该有效 - 参考:
https://*.com/a/9417254/1073631
Guessing that you're trying to update a primary key (unique) column with a value that is already in another row in that same table --- hence the duplicate entry error.
猜测您正在尝试使用已在同一表中另一行中的值更新主键(唯一)列 - 因此重复输入错误。
#2
1
I think the primary key for your Table1 is on PKey AND Col1, i.e.
我认为你的Table1的主键是PKey和Col1,即
CREATE TABLE `Table1`
...
PRIMARY KEY (`PKey`,`Col1`)
The error about the duplicate entry '199-1' points in that direction.
关于重复条目'199-1'的错误指向该方向。
#1
1
Your query above should work -- reference:
您的上述查询应该有效 - 参考:
https://*.com/a/9417254/1073631
Guessing that you're trying to update a primary key (unique) column with a value that is already in another row in that same table --- hence the duplicate entry error.
猜测您正在尝试使用已在同一表中另一行中的值更新主键(唯一)列 - 因此重复输入错误。
#2
1
I think the primary key for your Table1 is on PKey AND Col1, i.e.
我认为你的Table1的主键是PKey和Col1,即
CREATE TABLE `Table1`
...
PRIMARY KEY (`PKey`,`Col1`)
The error about the duplicate entry '199-1' points in that direction.
关于重复条目'199-1'的错误指向该方向。