CODEWITHSUNDEEP
javamysqlsql-insert
Alexander Sagen
Alexander Sagen

Reputation: 127

Insert int mysql without duplicates

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

Upvotes: 0

Views: 192

Answers (2)

abl
abl

Reputation: 5958

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 
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

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.

Upvotes: 1

Related Questions