Multiple unique entries on table update mysql

Question

Right now I have a table with an auto increment id number, a name, and a definition I am updating the table with values from another table using

INSERT INTO words(id, word, definition) SELECT id, word, definition FROM temp 
ON DUPLICATE KEY UPDATE words.word = temp.word, words.definition=temp.definition;

The table temp has the same exact column layout as the table I am using, and it works fine except when I delete an entry from the middle of temp and then update my main table. For example,

temp
id  word definition
1   bob   is a cat
2   sam   is a dog
3   doug  is a monk
4   croe  is a bird

table main is set to that Then I remove row 2 from temp

temp
    id  word definition
    1   bob   is a cat
    2   doug  is a monk
    3   croe  is a bird

and then update main table

main
id  word definition
1   bob   is a cat
2   sam   is a dog
3   croe  is a bird
4   croe  is a bird

because temp only has 3 rows now, but main HAD 4, the 4th row isn't deleted. I tried to fix this by making word a unique column, and now I get the error that there is a duplicate entry for key 'word'. So how do I fix this?

You forgot to add a unique index on main.word – symcbean Dec 09 '12 at 23:59 — symcbean, Dec 09 '12 at 23:59

score 0 · Accepted Answer · edited May 23 '17 at 11:55

0

From the MySQL manual,

In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

so, what you could do is make word your primary key, and get rid of the id column. That should do the trick :)

Your query then becomes

INSERT INTO words(word, definition) SELECT word, definition FROM temp 
ON DUPLICATE KEY UPDATE words.word = temp.word, words.definition=temp.definition;

If you need arbitrary sorting, I would create an additional table

CREATE TABLE word_order (VARCHAR(30) PRIMARY KEY, sort_order INT)

and then when you run your queries

SELECT words.word,
       words.definition
FROM words
JOIN word_order
ON words.word = word_order.word
ORDER BY word_order.sort_order

Your INSERTS become a little more complicated though, you'd have to run multiple inserts. See sql - insert into multiple tables in one query.

Also, this CodeProject article has some more ideas on the topic.

edited May 23 '17 at 11:55

Community

1
1

answered Dec 09 '12 at 23:48

Fabian Tamp

4,416
2
26
42

It works now, but it is automatically ordering the table based on the primary key. Do you know how to make it unordered? – rasen58 Dec 10 '12 at 00:31
So, you're asking if you can use this technique and still order the words however you want? – Fabian Tamp Dec 10 '12 at 00:54
Yeah, the words weren't originally organized, but when I made word the primary key, they became sorted alphabetically. so yes – rasen58 Dec 10 '12 at 01:58
Edited with some ideas on arbitrary sorting. – Fabian Tamp Dec 10 '12 at 02:51

Multiple unique entries on table update mysql

1 Answers1