Insert int mysql without duplicates

Question

I have a database { id, name, uuid, world, x, y, z }

How can i insert into this database, and if there exists any row where { world, x, y, z } are the same as in that I inserted, the old row gets deleted. I have id as PRIMARY KEY and set to AUTO_INCREMENT.

I searched this forum and google but didn't find anything that helped with my issue.

Thank you

Do you really need it deleted and replaced with a new id, or do you want it updated with the same id? — Joachim Isaksson
– Joachim Isaksson, Commented Dec 7, 2014 at 14:01
I'm not sure. I want it as simple as possible and to make as few queries to the database server as possible. If you have a solution where I keep the id, then that is perfect as long as it is done in one query? — Alexander Sagen
– Alexander Sagen, Commented Dec 7, 2014 at 14:32

abl · Accepted Answer · 2014-12-07 14:02:48Z

1

From the MySQL docs:

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, the old row is deleted before the new row is inserted.

So declare a UNIQUE index row columns (word, x, y, z) and then:

REPLACE INTO my_table (name, uuid, world, x, y, z)
VALUES (
    -- your values here 
)

answered Dec 7, 2014 at 14:02

abl

5,9684 gold badges27 silver badges44 bronze badges

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

1 Comment

Alexander Sagen Over a year ago

Accepted this for being the most direct answer.

Gordon Linoff · Accepted Answer · 2014-12-07 14:01:33Z

1

You would use insert . . . on duplicate key update:

insert into table(name, uuid, world, x, y, z)
    values (@name, @uuid, @world, @x, @y, @z)
    on duplicate key update name = values(name),
                            uuid = values(uuid);

The variables @name and so on are just place holders for your values. This also assumes that id is auto-incremented, so it doesn't need to be inserted.

For this to work, you need a unique index on the four columns:

create unique index idx_table_world_x_y_z on table(world, x, y, z);

If these are already declared as a primary key, then this is not necessary.

answered Dec 7, 2014 at 14:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Insert int mysql without duplicates

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related