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?
source to share
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
source to share
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
?
source to share
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!
source to share