CODEWITHSUNDEEP
phpmysql
iyal
iyal

Reputation: 1285

Find duplicate value on multi insert

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?

Upvotes: 3

Views: 198

Answers (7)

Prasanth
Prasanth

Reputation: 33

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);

Upvotes: 1

Mahsin
Mahsin

Reputation: 638

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

Upvotes: 7

Jamjam Akter
Jamjam Akter

Reputation: 31

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

Upvotes: 3

Aleksandr K.
Aleksandr K.

Reputation: 528

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?

Upvotes: 1

Riaydh
Riaydh

Reputation: 51

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.

Upvotes: 3

Zymon Castaneda
Zymon Castaneda

Reputation: 759

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!

Upvotes: 0

Purushottam zende
Purushottam zende

Reputation: 552

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.

Upvotes: 3

Related Questions