Replace Into without checking Auto Increment Primary Key?

Question

I have the following table (tbl_test) in MySQL server:

id    AUTO_INCRECMENT  PRIMARY
text1 VARCHAR(20)

and inserted a row of data:

INSERT INTO tbl_test (text1) VALUES ('Apple')

id    text1
===========
1     Apple

Then, I plan to use REPLACE INTO to check for existing value and insert if needed:

REPLACE INTO tbl_test (text1) VALUES ('Apple')

But it inserted a new row.

id    text1
===========
1     Apple
2     Apple

How can I check for existing data and insert only if needed (ignoring auto increment primary key)?

Answer 1

You can make use of UPSERT

INSERT INTO tbl_test
(`text1`)
VALUES( 'Apple')
ON DUPLICATE KEY UPDATE
`text1` = VALUES(text1);

Answer 2

As of official documentation REPLACE

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index

You have to specify already use primary key in your statement of for your text field add a unique index to see it in action

For existing primary key eg 1 use

REPLACE INTO `table1` (id,text1) VALUES (1,'Apple1');

DEMO

Another example for unique key

ALTER TABLE `table1` ADD UNIQUE INDEX `indexu` (`text1`); 

Sample data

INSERT INTO `table1` (id,text1) VALUES (1,'Apple');
INSERT INTO `table1` (id,text1) VALUES (2,'Banana');

Lets update id for value Banana

REPLACE INTO `table1` (id,text1) VALUES (5,'Banana');

DEMO