CODEWITHSUNDEEP
mysql
Raptor
Raptor

Reputation: 54252

Replace Into without checking Auto Increment Primary Key?

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

Upvotes: 6

Views: 9273

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 2

M3ghana
M3ghana

Reputation: 1281

You can make use of UPSERT

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

Upvotes: 8

Related Questions