Find duplicate value on multi insert

Question

I am inserting new record to table. The records can be more than one.

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

Then it will insert 10 records to 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 number of 1 to 10, there may be some numbers which have be already exist. Say that the number which have been already exist are the number of 4,6, and 9. If it is so, then I want to find this duplicate numbers to be alert or echo, (Hei, this value already exist 4,6,9(something like so)), and the rest numbers are keep inserted.

My question is, how to find this duplicate number?

My be this could help you: http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql — Piyush aggarwal, Apr 27 '15 at 04:45

score 7 · Answer 1 · edited Jul 26 '16 at 06:01

7

Source: AskBeen.com How to find duplicate records in MySQL table

For example, if you want to find duplicates of title:

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

edited Jul 26 '16 at 06:01

shA.t

16,580
5
54
111

answered Apr 27 '15 at 04:56

Mahsin

638
4
18

Thank you brother shA.t :) – Mahsin Jul 25 '16 at 12:23

score 3 · Answer 2 · answered Apr 27 '15 at 04:47

3

You can select the records first from the DB, for the values that you want to insert. If they exist then don't insert them or else insert them.

answered Apr 27 '15 at 04:47

Purushottam zende

552
1
6
20

score 3 · Answer 3 · answered Apr 27 '15 at 05:57

3

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

answered Apr 27 '15 at 05:57

Riaydh

51
3

score 3 · Answer 4 · answered Apr 28 '15 at 13:47

3

Read database book about relation between two columns and See the sql update section.

answered Apr 28 '15 at 13:47

Jamjam Akter

31
2

score 1 · Answer 5 · edited Jul 26 '16 at 06:02

1

Create a Unique key constraint on that particular column of your table where you don't want duplicates to be inserted.

ALTER TABLE table_name
  ADD CONSTRAINT constraint_name 
  UNIQUE (column_name);

edited Jul 26 '16 at 06:02

shA.t

16,580
5
54
111

answered Apr 27 '15 at 05:28

Prasanth

33
5

score 1 · Answer 6 · answered Apr 27 '15 at 08:15

A little 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 is there another way to use assignment to a variable in ON DUPLICATE KEY UPDATE clause?

score 0 · Answer 7 · answered Apr 27 '15 at 05:09

0

You can try this structure, mate:

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 based on the needs of your application. Cheers!

answered Apr 27 '15 at 05:09

Avidos

739
12
23

may I know what is @exist_record here? – iyal Apr 27 '15 at 06:04
the existing record(s) before the insert, mate. – Avidos Apr 27 '15 at 06:27

Find duplicate value on multi insert

7 Answers7