0

I have a INSERT...SELECT statement that I want to run several times from TABLE1 to TABLE2 as the data in TABLE1 will change overtime.

I don't expect the past data from select to change overtime, the select will just return more new data, and the same old data of previous queries.

But I cannot insert old data again because of the unique key constraint on the primary keys.

What I would like to achieve is a kind of INSERT ...ON DUPLICATE KEY UPDATE... but for a INSERT...SELECT. Skipping the duplicate keys, will be enough, but I'm not sure how to do it

  • Use NOT EXISTS for to copy only those records which not exists in destintion table yet. Or try INSERT IGNORE .. SELECT if some unique index which will prevent duplicates indsertions exists. PS. INSERT .. SELECT .. ON DUPLICATE KEY UPDATE .. exists too. – Akina Feb 24 at 11:45
0

There is no MERGE statement in MySQL. You can do this quite easily as follows (all DDL and DML shown at bottom of post and also in the fiddle here):

I created and populated two tables foo_1 and foo_2 with identical data and identical PRIMARY KEYs, the fields (foo_x, foo_y);

I then added a record to table foo_1.

Then I performed the query:

INSERT INTO foo_2 
SELECT * FROM foo_1 WHERE (foo_x, foo_y) -- foo_x, foo_y are the PK!
NOT IN (SELECT foo_x, foo_y FROM foo_2);

And then SELECT * FROM foo_2 and the new record from table foo_1 had been copied over to table foo_2. This is a simple and effective method of doing what you want as far as I can see. Doing this with a single field PRIMARY KEY is even easier!

=============== DDL and DML ============

table foo_1:

CREATE TABLE foo_1
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_1 
VALUES 
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

and table foo_2:

CREATE TABLE foo_2
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_2
VALUES 
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

Insert new record into table foo_1:

INSERT INTO foo_1 VALUES (1, 5, 24, 'foo_3_val_5');

And then:

INSERT INTO foo_2 
SELECT * FROM foo_1 WHERE (foo_x, foo_y)
NOT IN (SELECT foo_x, foo_y FROM foo_2);

And then check that table foo_2 now contains the single new record from table foo_1:

SELECT * FROM foo_2;

Result:

foo_x   foo_y   foo_2   foo_3
1       1           2   foo_3_val_1
1       2           7   foo_3_val_2
1       3           8   foo_3_val_3
1       4           9   foo_3_val_4
1       5          24   foo_3_val_5
  • do you mean foo_x will be the real primary key of foo_1 and that in this way we are keeping track of correspondence between PK of foo_1 and PK of foo_2? – Glasnhost Feb 24 at 11:35
  • Well, in order to avoid duplicates, you have to have a PRIMARY KEY. I used a multi-field one in order to show this which can intimidate people who aren't experienced. Check out the DDL I've posted at the end or, even better, the fiddle - I had forgotten the link. Any further questions, don't hesitate to ask. You could also have a UNIQUE index! – Vérace Feb 24 at 11:38
0
INSERT IGNORE INTO t2
    SELECT ... FROM t1 ...;

sounds like what you want. Notes:

  • It will "IGNORE" any t1 rows which match on a UNIQUE key (including the PRIMARY KEY), inserting only the rest.
  • If t2 has an AUTO_INCREMENT column (and it is not being used for 'IGNORE'), this command is likely to "burn" ids. (IODKU also burns ids.) This is because the operation effectively discovers how many rows are coming from the SELECT and allocate that many ids before realizing that some will not be used.
  • Because of both of the above notes, it may be better to have a "natural PK", not an auto_inc.
  • IODKU is like INSERT IGNORE plus the ability to change non-unique columns, set modified_date, count the number of changes, etc.
  • If you must have an auto_inc, but the "burning of ids" is a concern, you might need to go to BIGINT. Or you can use a 2-step technique discussed here: http://mysql.rjweb.org/doc.php/staging_table#normalization -- I like it for "batch normalization"; it is designed for high-volume ingestion of data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.