1

I have two tables with identical structure in two different DBs. I want to insert records from one into the other, but I cannot be sure there are no duplicate primary keys. Hence I want to renumber the primary keys in the source table to avoid the problem, by making them bigger than any key used in the target table.

(In doing so I have discovered what should have been obvious, that I also have to make the new value bigger than any existing value in the source table!)

In my code, the source table is VDA_test and the target is DCS_test. Here is my code to perform the renumbering of the primary keys of one table

UNLOCK TABLES;
LOCK TABLES DCS_test.tblActions READ, VDA_test.tblActions WRITE;
UPDATE VDA_test.tblActions SET pkAction = pkAction + (SELECT MAX(pkAction) FROM DCS_test.tblActions) + (SELECT MAX(pkAction) FROM VDA_test.tblActions) WHERE pkAction > 0;

"WHERE pkAction > 0" is just there to stop WorkBench complaining about "unsafe" updates (even though I've turned off the warning)

The code yields

Error Code: 1100. Table 'tblActions' was not locked with LOCK TABLES

If I don't include

 + (SELECT MAX(pkAction) FROM VDA_test.tblActions)

I don't get the error (but I hit a duplicate)

This code gives no error,

UNLOCK TABLES;
SELECT MAX(pkAction) INTO @pkActionMax FROM VDA_test.tblActions;
LOCK TABLES DCS_test.tblActions READ, VDA_test.tblActions WRITE;
UPDATE VDA_test.tblActions SET pkAction = pkAction + (SELECT MAX(pkAction) FROM DCS_test.tblActions) + @pkActionMax WHERE pkAction > 0;

but it isn't "safe", in that the maximum PK in the source is derived before the table is locked. Is there a safe way to this?

(P.S. I want to acknowledge the comment of fool4jesus on Simin Jie's answer to MySQL - Table 'my_table' was not locked with Lock Tables which has helped me get this far)

DJDave
  • 865
  • 1
  • 13
  • 28
  • how about locking table `VDA_test.tblActions` both for read and write, not just for write(you're reading it now)? – Alexey Dec 15 '19 at 19:51
  • Cannot you get the MAX value into session variable first? This will simplify UPDATE query. – fifonik Dec 15 '19 at 22:14
  • Alexey, how do you do that? I've tried LOCK TABLES auditrec_BCS_test.tblActions READ, auditrec_VDA_test.tblActions READ, auditrec_VDA_test.tblActions WRITE; and it results in Error Code: 1066. Not unique table/alias: 'tblActions' I don't see a syntax option for saying both at the same time – DJDave Dec 16 '19 at 07:58
  • fifonik, that's just what I'm trying to avoid! The table won't be locked between finding the MAX value and using it (and so it could change) – DJDave Dec 16 '19 at 08:00

0 Answers0