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

Can you use unique index on column?

Sahil Jain
– Sahil Jain

2015-02-03 10:10:08 +00:00
Commented Feb 3, 2015 at 10:10 — Sahil Jain
– Sahil Jain, Commented Feb 3, 2015 at 10:10
I can use it on the columns.

Raptor
– Raptor

2015-02-03 10:17:20 +00:00
Commented Feb 3, 2015 at 10:17 — Raptor
– Raptor, Commented Feb 3, 2015 at 10:17

M3ghana · Accepted Answer · 2015-02-03 09:55:27Z

8

You can make use of UPSERT

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

answered Feb 3, 2015 at 9:55

M3ghana

1,3011 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raptor Over a year ago

this works great. the auto increment id does not get updated either.

BrainStone Over a year ago

Note that this does no longer work with InnoDB. If you want this to work you have to use MyISAM (which comes at a slight performance penalty that gets worse the more concurrent access there are).

M Khalid Junaid · Accepted Answer · 2015-02-03 09:59:43Z

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

edited Feb 3, 2015 at 9:59

answered Feb 3, 2015 at 9:53

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

2 Comments

Raptor Over a year ago

works like charm, but is there any chance that the Auto ID won't get updated? Currently the ID will change once REPLACE INTO is executed. Or I should use 2 queries (1 for checking existence, 1 for inserting?)

M Khalid Junaid Over a year ago

@Raptor my second example is for unique index and for explanation i have updated id column but it doesn't mean it will update primary key you can specify other columns in query and when there is any value repeated then it will update that row instead of creating new one

Collectives™ on Stack Overflow

Replace Into without checking Auto Increment Primary Key?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related