Unsure on SQL for INSERT... ON DUPLICATE UPDATE with two columns

[nemovc]

Hi

 

I have a table 'contributions'

 

CREATE TABLE `op_contributions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attachID` int(11) NOT NULL,
  `playerID` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  `e` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

 

The playerID is the ID (primary key) of a row in another table (players), and the attachID is the ID (primary key) of a row in another table (plans). (a to e are data fields).

 

attachID and playerID don't have to be unique, but the combination of them has to be. E.g., there are many different plans, and each has a different players contributing different amounts, but each player can only contribute once to each plan.

 

I am unsure how to create a INSERT ... ON DUPLICATE UPDATE query when it is the combination of two columns that is important, not a singular column.

 

(At the moment I am using the messy workaround of loading the all the plans into php and then either UPDATE or INSERT depending on if a record is already found)

 

Response greatly appreciated

[fenway]

You just need to have your unique key span both columns.

[ManiacDan]

Make another unique index for that combination of columns.  The "on duplicate" condition will trigger when a unique key (primary or otherwise) is violated.

[nemovc]

Thanks guise, I have got this working now. I didn't know how indexes spanning multiple columns worked... but I do now, thanks!