MySQL insert new rows and ignore existing rows, based in many columns

Question

I have a table where I want to insert new rows like

ID   |  MAGICID |  PERSONREF |  DATA
1    |        1 |    0000010 |  any trash data
2    |        1 |    0000011 |  any trash data
3    |        1 |    0000012 |  any trash data
4    |        1 |    0000013 |  any trash data
5    |        2 |    0000010 |  any trash data
6    |        2 |    0000011 |  any trash data
7    |        2 |    0000012 |  any trash data
8    |        2 |    0000013 |  any trash data
9    |        3 |    0000010 |  any trash data
10   |        3 |    0000011 |  any trash data
11   |        3 |    0000012 |  any trash data
12   |        3 |    0000013 |  any trash data

In table ID is a PK with INT(10), MAGICID is a FOREIGN KEY to another TABLE with INT(10) , PERSONREF is a normal column with VARCHAR(8)

For me, a duplicate row is when MAGICID and PERSONREF is same

ID   |  MAGICID |  PERSONREF |  DATA   ---Table without duplicates, is OK
1    |        1 |    0000010 |  any trash data
2    |        1 |    0000011 |  any trash data

_

ID   |  MAGICID |  PERSONREF |  DATA   ---Table with duplicates, is bad
1    |        1 |    0000010 |  any trash data
2    |        1 |    0000010 |  any trash data

A insert should work like this

-- table before insert
ID   |  MAGICID |  PERSONREF |  DATA
1    |        1 |    0000010 |  any trash data
2    |        1 |    0000011 |  any trash data
3    |        1 |    0000012 |  any trash data
4    |        2 |    0000015 |  any trash data

The inserts

INSERT .... (1 , '0000010' ,'trash')
INSERT .... (1 , '0000011' ,'trash')
INSERT .... (1 , '0000012' ,'trash')
INSERT .... (1 , '0000015' ,'trash')
INSERT .... (1 , '0000016' ,'trash') -- in each procces MAGIC is the same for all inserts

After look...

ID   |  MAGICID |  PERSONREF |  DATA
1    |        1 |    0000010 |  any trash data
2    |        1 |    0000011 |  any trash data
3    |        1 |    0000012 |  any trash data
4    |        2 |    0000015 |  any trash data
41   |        1 |    0000015 |  trash --beacuse is new for this MAGICID ,ID is simulated :)
42   |        1 |    0000016 |  trash --beacuse is new for this MAGICID, ID is simulated :)

can you help me?

eggyal · Accepted Answer · 2015-04-23T17:51:06.083

1

Create a UNIQUE constraint over the composite (MAGICID, PERSONREF):
```
ALTER TABLE myTable ADD UNIQUE (MAGICID, PERSONREF);
```

Use INSERT ... ON DUPLICATE KEY UPDATE:

INSERT INTO myTable
  (MAGICID, PERSONREF, DATA)
VALUES
  (1, '0000010', 'trash'),
  (1, '0000011', 'trash'),
  (1, '0000012', 'trash'),
  (1, '0000015', 'trash'),
  (1, '0000016', 'trash')
ON DUPLICATE KEY UPDATE
  DATA = DATA
;

See it on sqlfiddle.

edited Apr 23 '15 at 17:51

answered Apr 23 '15 at 17:47

eggyal

122,705
18
212
237

i suppose that `ON DUPLICATE KEY UPDATE DATA = VALUES(DATA)` was update `DATA`, but I want keep the old, I only remove this to do it? – jasilva Apr 23 '15 at 17:49
@jasilva: Oh. My misunderstanding. You still need to specify at least one column to update, but could just set whichever you like to itself e.g. `... ON DUPLICATE KEY UPDATE DATA = DATA`. An alternative would be to do `INSERT IGNORE ...` after defining the `UNIQUE` key, and not worry about `ON DUPLICATE KEY UPDATE`, however that will ignore *all* errors—not just duplicate key errors—so could lead to unintended consequences. See [“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”](http://stackoverflow.com/a/548570) for more information. – eggyal Apr 23 '15 at 17:53
Ok no problem, this a great answer for me, thanks for the clarification – jasilva Apr 23 '15 at 18:00

score 1 · Answer 2 · answered Apr 23 '15 at 17:58

For this example your gave, INSERT .... (1 , '0000010' ,'trash'), you would have to do something like this:

INSERT INTO your_table (MAGICID , PERSONREF, DATA) 
SELECT 1, '0000010', 'trash' FROM DUAL 
WHERE NOT EXISTS(SELECT ID
                FROM you_table
                WHERE MAGICID = 1 AND PERSONREF = '0000010')

This will basically insert 1, '0000010', 'trash' if the combination of MAGICID and PERSONREF is unique

MySQL insert new rows and ignore existing rows, based in many columns

2 Answers2