0

According to the docs:

If [columns a and b are] unique, the INSERT is equivalent to this UPDATE statement instead: 

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

This is fair enough, but what if I have this as the only key:

PRIMARY KEY (`a`,`b`)

Since the duplicate key is dependant on both fields simultaneously, would the update reliably affect the specific row where the duplicate occurs, or does it do the same as if the fields were individually unique?

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592

2 Answers2

1

Assuming you're using the same query as in your example, it wouldn't reliably update the row with the duplicate key. It would still find the first row in data order that has either of the matching values. Consider the example below.

   a | b
1. 1 | 1
2. 1 | 2
3. 1 | 3
4. 1 | 4
5. 2 | 1
6. 2 | 2

the query UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; would update the first row, not the desired second row. So in a few words, it's the same as if the columns were individually unique.

G-Nugget
  • 8,666
  • 1
  • 24
  • 31
  • But what would happen if I ran `INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1`? – Niet the Dark Absol Aug 28 '12 at 02:59
  • I see now that I misunderstood your original post. That would update the duplicate row since it's basically the same as a duplicate on a single key. – G-Nugget Aug 28 '12 at 03:10
0

If the on duplicate concerns a column witch is define as unique or primary, or the SAME set of columns defined in a unique or primary key, an insert ... on duplicate update ... statement will update the row where ALL the columns in this PK or unique key have the same values.

To answer your comment on G-Nugget answer, only the row 2 will be updated.

Hope that helps ;-)

Huafu
  • 2,445
  • 1
  • 22
  • 26