2

This seems like it should be simple, but I couldn't figure out a way to do it. Let's say I have a table with 5,000 rows, each with an ID (primary key) of 1–5000. I am blindly inserting a new value with an existing ID, and it could be something like 2677. What I want to happen is that if the ID already exists, it will use the auto_increment value, in this case 5001. That or the maximum existing value + 1.

Most importantly, I can't use PHP (or anything else other than SQL) to do this, because the output is a query that needs to be directly importable without errors.

I have looked at two similar questions on SO:

Community
  • 1
  • 1
Ynhockey
  • 3,845
  • 5
  • 33
  • 51
  • Where would data be coming from that has a duplicate key? If duplication was not important then the better approach would be query the database for the current max ID, add one and submit that for insert avoiding the issue altogether. – Lazarus Sep 11 '16 at 09:45
  • Can this be done in the same query? Basically I am creating an automatic dump for certain data, it has to run like a regular MySQL dump. – Ynhockey Sep 11 '16 at 09:51
  • I don't see the query so impossible to answer. – Lazarus Sep 11 '16 at 09:51
  • There is no query. Currently it's just inserts, for example: INSERT INTO table (id, name) VALUES (2352, 'random name'). This doesn't always work because often IDs collide, but I need to be able to insert everything. – Ynhockey Sep 11 '16 at 10:23
  • Any option to make the ID field an identity field with autoincrement and just not use it for the insert? – Lazarus Sep 11 '16 at 10:32
  • This is a last-resort scenario. It would really be undesirable, since in practical cases for this importer, about 90% of the IDs will actually stay the same. This will make it easier to compare later and do some other operations. – Ynhockey Sep 11 '16 at 11:14
  • Your id seems to not have any special meaning. After an import, the id can be "id" or it can be any other value. So you may be able to not use it at all. Insert all data without specifying the autoincrement id (or save it to a non-unique column if you are still interested in the value), and it will generate a new id for every row. If you are trying to do some duplicate check in one step, it won't work, do it in two steps. Importing data in two steps (e.g. a temp table) is a safer way anyway. – Solarflare Sep 11 '16 at 16:39
  • If you say you want to insert id 2444 and if free, insert it, if taken, heck, take 5001, then your id's didn't have much importance to begin with. So I agree with @Solarflare – Drew Sep 11 '16 at 18:25
  • There is another valid use case for this behavior that @Ynhockey is asking for. I have a normalized table; it has a physical primary key (an auto-increment column), a logical primary key, and another column that has a comma-separated list of a secondary key. I now want to de-normalize this table, breaking a row with `n` comma-separated secondary-key values into `n` rows, but I want to maintain the physical primary key value for one of those `n` rows and assign new auto-increment values for the remaining rows. (Background: This is the reverse operation of a migration to *normalize* this table.) – Julian Mehnle Jul 24 '20 at 18:40

1 Answers1

0
INSERT INTO table (column1,column2) VALUES (1,2) ON DUPLICATE KEY UPDATE id=VALUES(id)

Obviously this requires an id column with AUTO_INCREMENT.

Moreover if you later need to select the inserted id just like if it was a new Insert, you do:

ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(VALUES(id));
Vixxs
  • 569
  • 7
  • 21