Question:I have a spreadsheet that needs to be uploaded. But each row needs to be check if they are unique in the database. One option that I can think of is to check each row exists in the database or not, that means doing N sql queries for N rows. Is there any alternatives on effectively checking for unique data instead of checking row by row?
Answer:we can do that by using NOT EXISTS and NOT IN methods. To do that, first we need to insert that spreadsheet data’s into one temp table.
lets take your main table as TABLE_1 and your spreadsheet temp table as TABLE_2.
Using NOT IN –
INSERT INTO TABLE_1 (id, name) SELECT t2.id, t2.name FROM TABLE_2 t2 WHERE t2.id NOT IN (SELECT id FROM TABLE_1)
Using NOT EXISTS –
INSERT INTO TABLE_1 (id, name) SELECT t2.id, t2.name FROM TABLE_2 t2 WHERE NOT EXISTS(SELECT id FROM TABLE_1 t1 WHERE t1.id = t2.id)
If you have better answer, please add a comment about this, thank you!
If you like this answer, you can give me a coffee by click here (view Ads)