2

I have to insert a few values into a mysql table, but only if there are no other (almost) equal rows, for example:

TABLE T
KEY | COL1 | COL2
1   | abc  | 123
2   | def  | 456

The KEY column uses auto increment, and I don't inform it on the statement, like this:

INSERT INTO T (COL1, COL2) VALUES (abc, 123)

The statement above have the same values as the first row. How can I inform mysql that I don't want to insert if the row is a duplicate like that?

I googled for some solutions and found INSERT IGNORE, ..ON DUPLICATE KEY UPDATE and REPLACE in which I would have to inform the PK, but I don't know it (without using a extra query).

Lucas Noetzold
  • 1,670
  • 1
  • 13
  • 29

3 Answers3

2

you can do like... 

>   INSERT INTO memos(id,text) 
>     SELECT 5, 'text to insert' 
>     WHERE NOT EXISTS(SELECT 1 FROM memos WHERE id = 5 AND text = 'text to insert');
Developer
  • 333
  • 2
  • 16
1

ON DUPLICATE KEY and INSERT IGNORE will work with any unique index, not just the primary key. So you can add a unique index for these columns:

ALTER TABLE T
ADD UNIQUE INDEX (col1, col2);

Making it a multi-column index means that the combination has to be unique, even though each column can be duplicated individually.

Barmar
  • 741,623
  • 53
  • 500
  • 612
0
if not exists(select * from t where col1 = "abc" and col2 ="123")then 
  insert into t (col1,col2) values("abc",123 );
end if;
loki
  • 31
  • 6