Clever Geek Handbook

Find Duplicate Value for Multiple Insert

I am inserting a new record into a table. There can be more than one entries.

If I insert the value 1,2,3,4,5,6,7,9,10

Then it inserts 10 records into the table.

   INSERT INTO table (record) VALUES (1,2..,10) ON DUPLICATE KEY UPDATE record=record 
   //Actually on duplicate key I want to update whole column here (not only the record column on its row

In the range from 1 to 10 there may be some numbers that already exist. Let's say that the number that already exists is the number 4,6 and 9. If so, then I want these repeating numbers to be alert or echo (Hei, this value already exists 4,6, 9 (something like this)), and the rest of the numbers are kept.

My question is, how do I find this duplicate?

+3


source to share


7 replies


Source: AskBeen.com Find Duplicate Records in MySQL Table

For example, if you want to find duplicates title

:



SELECT COUNT(*) c, title 
FROM `data` 
GROUP BY title 
HAVING c > 1
+7


source


You can first select records from the DB for the values ​​you want to insert. If they exist, do not insert or insert them.



+3


source


You can select the database fed from your table of records and then insert the data. If they are present in the database row then don't insert them if they are missing then insert the data.

+3


source


Read the database about the relationship between the two columns and see the sql update section.

+3


source


Create a unique key constraint on that specific column of the table where you don't want duplicates to be inserted.

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name 
  UNIQUE (column_name);
+1


source


A bit ugly but working solution:

SET @uids := '';
INSERT INTO table (record) VALUES (1,2..,10) ON DUPLICATE KEY UPDATE record = IF(@uids := CONCAT_WS(',', record, @uids), record, record);
SELECT @uids;

I wonder if there is another way to use variable assignment in an expression ON DUPLICATE KEY UPDATE

?

+1


source


You can try this framework buddy:

START TRANSACTION;
SELECT record INTO @exist_record FROM table WHERE record IN (1,2..,10);
INSERT INTO table (record) VALUES (1,2..,10) ON DUPLICATE KEY UPDATE record=record;
SELECT @exist_record;
COMMIT;

Just edit or use it depending on the needs of your application. Hooray!

0


source






More articles:


All Articles