mysql REPLACE query with multiple primary keys

Question

so MYSQL's REPLACE command (not to be confused with the string replace function) replaces a row if there exist a column with the same primary key with the inserted data...

but what if I have two primary keys and I want to use both to specify the row to replace not just one of them....how do I specify mysql to use both keys rather than just one

Answer 1

It shouldn't make a difference, it's the same syntax. Just be sure you have both keys specified as columns. For example:

REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` /* , ... */ )
  VALUES ( 'widgets', 14, 'Blue widget with purple trim' );

EDIT

Here's my test I ran in my test database to make sure I wasn't about to destroy your data. Of course, I encourage you to try it out if you're unsure!

CREATE SCHEMA `my_testdb`;
USE `my_testdb`;
CREATE TABLE `my_table` (
  `key1` VARCHAR(20) NOT NULL,
  `key2` INTEGER NOT NULL,
  `othercolumn1` VARCHAR(50),
  CONSTRAINT PRIMARY KEY (`key1`, `key2`) );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'Green widget with fuchsia trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 15, 'Yellow widget with orange trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'thingamabobs', 14, 'Red widget with brown trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'Blue widget with purple trim' );
SELECT * FROM `my_table`;

This is my result:

key1          key2  othercolumn1
widgets       14    Blue widget with purple trim
widgets       15    Yellow widget with orange trim
thingamabobs  14    Red widget with brown trim

ANOTHER EDIT

I think I see what you're talking about in the documentation, the confusion over unique columns:

It is possible for a single row to replace more than one old row if the table contains multiple unique indexes and the new row duplicates values for different old rows in different unique indexes. —MySQL Documentation

That's referring to a rather contrived circumstance in which the row you're replacing with conflicts not just with an existing primary key, but with other unique columns as well. Here's another example to illustrate this point:

CREATE SCHEMA `my_testdb2`;
USE `my_testdb2`;
CREATE TABLE `my_table` (
  `key1` VARCHAR(20) NOT NULL,
  `key2` INTEGER NOT NULL,
  `color` VARCHAR(20) NOT NULL UNIQUE,
  `othercolumn1` VARCHAR(50),
  CONSTRAINT PRIMARY KEY (`key1`, `key2`) );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'green', 'Green widget with fuchsia trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 15, 'yellow', 'Yellow widget with orange trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'thingamabobs', 14, 'red', 'Red widget with brown trim' );
REPLACE INTO `my_table` ( `key1`, `key2`, `color`, `othercolumn1` )
  VALUES ( 'widgets', 14, 'yellow', 'Yellow widget with purple trim' );
SELECT * FROM `my_table`;

Notice how the last REPLACE operation not only conflicts with the (key1, key2) primary key, of the first REPLACE, but also with the unique color of the second one. In this case, BOTH rows are deleted before the last REPLACE operation is performed so that the result is no conflict. You'll end up with just two rows:

key1          key2  color   othercolumn1
widgets       14    yellow  Yellow widget with purple trim
thingamabobs  14    red     Red widget with brown trim

Both the row with (key1, key2) equal to ('widgets', 14) AND the row with the color 'yellow' were blown away due to the new row conflicting with multiple unique constraints on the table.

Hope this helps!