-1

I had a statement in PSQL that did this just fine (based on an answer from this thread) But I've been trying to recreate the same statement in MySQL which has been difficult. My current workaround is ugly:

    CREATE TEMPORARY TABLE potential_duplicates (
        id VARCHAR(64)
        content TEXT,
    
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
    INSERT INTO potential_duplicates(id, content) VALUES ('1', 'some content'), ('2', 'some more content');
    SELECT id, content FROM potential_duplicates WHERE id IN (SELECT id FROM main_table);
    INSERT INTO main_table(id, content)
       SELECT id, content FROM potential_duplicates WHERE id NOT IN (SELECT id FROM main_table);

This query is going to be used very often and I feel like making a temporary table every query is inefficient. Originally I was trying to find a way to use the INSERT ON DUPLICATE feature. It would have worked fine if I was just inserting and nothing else, but i need to return the duplicate values in the end.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Vincent Lauffer
  • 78
  • 6

1 Answers1

1

Assuming id is a primary key (or at least unique), you can use INSERT IGNORE to avoid duplicates and RETURNING (available as of MariaDB 10.5) to return the inserted id values:

INSERT IGNORE main_content(id, content) 
VALUES ('2', 'some content'), ('5', 'some more content')
RETURNING id

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • WOW this is so much better! I thought that the returning would return all values, but glad to know it doesnt – Vincent Lauffer Mar 22 '22 at 04:02
  • @VincentLauffer the docs say it returns the values from the "inserted" rows, but I agree its unclear whether they mean the rows that were actually inserted, or the ones in the insert. – Nick Mar 22 '22 at 21:19