使用表B上的子选择(按重复键更新排序)向表A中插入

我有一个表,我想使用表a,然后将每个结果行插入/更新到表B中。目前我有这样的想法:

delete from tableB;
set @place = 0;
INSERT INTO tableB (id, place, name, colD, colF) SELECT (id, greatest(@place := @place + 1, 0) from tableA order by colD desc;

这是可行的,但我不想每次都从tableB中删除,因为该表可能包含数十万行。所以,我想在重复密钥更新中使用,因为每个id列都被设置为主键。我已经尝试了以下的各种版本(将ON-DUPLICATE-KEY-UPDATE子句移动)

INSERT INTO tableB (id, place, name, colD, colF) SELECT (id, greatest(@place := @place + 1, 0) from tableA order by colD desc ON DUPLICATE KEY UPDATE tableB.place = @place, tableB.name = tableA.name, tableB.colD = tableA.colD, tableB.colF = tableA.colF;