on duplicate key update

[facarroll]

I would like to be able to do the following pseudo-code using ON DUPLICATE KEY UPDATE, but I cannot find one unique key to nominate. Can I use ON DUPLICATE KEY UPDATE with the use of a combination of non-unique keys such as $id and $address which become unique upon combination? I use MySql v5.5.8

 

if ($id exists in table AND $address exists in table) { UPDATE field1=value1, field2=value2  where id=$id AND address=$address } else { insert into table field1=$value1, field2=$value2 }

 

Thanks.

[gizmola]

ON DUPLICATE KEY will fire when there is a key violation for either the primary key or another unique key.  So yes, as long as you have defined a unique key on the columns in question, you can use ON DUPLICATE KEY.

[The Little Guy]

You can add a multi-key which contains two columns.

 

Name      Age
-----------------
Ryan       12
Ryan       13
Ryan       12

 

So using the above, with a unique key on "Name" and "Age" (to make one unique key),

Ryan 12 and Ryan 13 are two different things thus an "on duplicate key update" won't occur.

But...

Ryan 12 and Ryan 12 are the same thing so thus "on duplicate key update" will occur, when the second one get added with an insert.

 

When storing the keys, MySql visually stores them like so (I don't know how MySQL physically stores them):

Ryan-12

Ryan-13

Ryan-45

Ryan-38

 

and so on where Ryan is from column one and the number is from column two.

[facarroll]

I get it. Thanks.