0

I have seen some questions with a similar title but they do not answer my question

I have one table like this

id  original    from_lang   translated  to_lang  ranking
 1   word1          es        trans1      en       0
 2   word1          es        trans2      en       1
 ...

id column is a UNIQUE PRIMARY KEY with AUTOINCREMENT

The rest of the columns can not be unique

What I try to do: When I insert a new row, if the original column and from_lang column and ranking column are the same then update existing row but if not the same then insert a new entry row

I have tried this

INSERT INTO words
    (original, from_lang, translated, to_lang, ranking)
VALUES
    ('word1','es','trans3','en',0)

-- ... here is mi problem because I don't know how to use 
-- ON DUPLICATE KEY UPDATE in my case that I have not UNIQUE columns

But the above query insert a new row

Espected: Update first row where id = 1

MTK
  • 3,300
  • 2
  • 33
  • 49
  • 1
    Don't you mean if original, from_lang, to_lang and ranking are the same ? – Paul Janssens Mar 19 '18 at 16:19
  • Is this directly in MySQL or are you using something like PHP to create the query? – James Mar 19 '18 at 16:19
  • @PaulJanssens Yes you have reason my fault I will edit – MTK Mar 19 '18 at 16:20
  • You'll get answers that will be bad and will only appear to be working. The solution is to have a unique index. There's no reason why you can't have one. Your problem is figuring out how to place the unique index, not how to avoid it. – N.B. Mar 19 '18 at 16:21
  • 1
    @MTK take a look at https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – Peter M Mar 19 '18 at 16:23
  • @Rockhopper I can use PHP to create query .. also a stored procedure but I try to avoid to many query to check – MTK Mar 19 '18 at 16:23
  • @N.B. You have also reason `Your problem is figuring out how to place the unique index, not how to avoid it` ... So how to place a unique index to acheive that ? – MTK Mar 19 '18 at 16:26
  • 2
    You have already answered that yourself, create a unique index containing original, from_lang and ranking – Joakim Danielson Mar 19 '18 at 16:27
  • @PeterM ... It seem that is what I need (I have checked your link) – MTK Mar 19 '18 at 16:28
  • 1
    Add a new field, call it `unique_id`. Make it `BINARY(32)`. Store the hash of `original + from_lang` into that field. You can create a trigger that does that for you. Problem solved, your unique index will always be of fixed length and your `ON DUPLICATE KEY` will work. – N.B. Mar 19 '18 at 16:28
  • @N.B. Ok thanks. I did not know that. your suggestion Peter M suggestion and Joakim Danielson suggestion seem to be the right solution for me – MTK Mar 19 '18 at 16:31
  • @N.B. `index will always be of fixed length` ... That made your suggestion to be the better (in mi case ) because I want to expand this to phrases also – MTK Mar 19 '18 at 16:41

0 Answers0