[SOLVED] Help with INSERT / ON DUPLICATE UPDATE

[phpdolan]

This should have a simple solution, but I'm not getting it.

 

I need to store employee, monthly schedules in a db. Initially the php query will INSERT, and it will run automatically, so later this query will UPDATE. I thought the following code might work, but it overwrites November with October instead of Inserting a new record. Of course it leaves behind other previously created values for November.

 

INSERT INTO Schedule ( empNum, MonthYr, bidLine ) VALUES ( '1234', 'October 2008', '8011' ) ON DUPLICATE KEY UPDATE bidLine = '8011', empNum = '1234', MonthYr = 'October 2008'

 

The WHERE clause doesn't seem to work in this setting and the manual for 5.0 doesn't include it.

 

What I want is to be able to update bidLine (and 35 other pieces of the row). I think this is dual keys or something like that.

 

How do I accomplish this?

 

David

[Barand]

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

[phpdolan]

Thanks for pointing out that page Barand. I've been up and down it several time and don't see how to write the syntax for 2 key query. I've also checked out the page regarding the syntax for INSERT/ON DUP. That is why I asked for help.

 

I've tried modifying the query a dozen times with mixed results, but never leaving the employee number AND month alone IF they exist and creating a NEW row with those 2 values unique.

 

Do you have another place to point? Or can anyone offer a more descriptive place.

 

David

 

[phpdolan]

To anyone interested,

 

If you followed the problem, then the solution was to create a composite key (of empNum and MonthYr) with a different name, and use the following syntax:

 

INSERT INTO Schedule ( empNum, MonthYr, bidLine, col4, col5 ) VALUES ( '1234', 'October 2008', '8011', 'whatever', 'whoever' ) ON DUPLICATE KEY UPDATE bidLine = '8011', col4 = 'whatever, col5 = 'whoever'

 

This way, the empNum and MonthYr are combine to be unique.

 

Happily solved on my own.