Solved

ON DUPLICATE KEY UPDATE with AUTO INCREMENT pk

Posted on 2015-02-14
4
437 Views
Last Modified: 2015-02-16
I've found, in my code, I use ON DUPLICATE KEY UPDATE but the primary key is autoincrement, so surely it would always insert a new record and never need the UPDATE part?

$sql = sprintf("INSERT INTO fixtures_results (id, fr_match_date, fr_season_lit, fr_season_start, fr_team_code, fr_compdiv,
                   fr_othercomp, fr_opposition, fr_otheroppo, fr_home_away, fr_venue, fr_alt_venue, fr_mapref,
                   fr_kickoff, fr_wld, fr_fobg_score, fr_oppo_score, fr_fobg_scorers, fr_oppo_scorers, fr_referee, fr_assistants,
                   fr_double_banker, fr_postponed_flag, fr_postponed_reason)
                   VALUES ('','%s','%s',%d,'%s','%s','%s',
                   '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                   '%s','%s','%s',%d,%d,'%s'),
                    ON DUPLICATE KEY UPDATE fr_match_date = '%s', fr_season_lit = '%s',
                    fr_season_start = %d, fr_team_code = '%s',
                    fr_compdiv = '%s', fr_othercomp = '%s', fr_opposition = '%s',
                    fr_otheroppo = '%s', fr_home_away = '%s', fr_venue = '%s',
                    fr_alt_venue = '%s', fr_mapref = '%s', fr_kickoff = '%s',
                    fr_wld = '%s', fr_fobg_score = '%s', fr_oppo_score = '%s',
                    fr_fobg_scorers = '%s', fr_oppo_scorers = '%s',
                    fr_referee = '%s', fr_assistants = '%s',
                    fr_double_banker = %d, fr_postponed_flag = %d,
                    fr_postponed_reason = '%s'",$matchdate, $seasonlit, $seasonstart, $side, $comp, $otherComp, $oppo, $otherOppo, $homeaway, $fn_address,
                                             $othervenue, $mapref, $ko, $wld, $fobgscore, $opposcore, $fobgscorers, $opposcorers, $referee, $assistants,
                                                         $double_banker, $postponed_flag, $postponed_reason);
0
Comment
Question by:colinspurs
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 40610474
the ID columns is often not used in an insert thus is not the reason a duplicate key is triggered, a unique index of a single column or a combination of columns will be the trigger for the duplicate event.

In your case though, I am not sure why you are including the ID in the insert directive?
What or how do you maintain the ID within your application?
Your insert into should exclude the id column as it is as you say autoincrement, if the record is validated and the columns are inserted, the ID column will be autoincremented and any other column that is not included in the insert directive but has a default value or null will be set to the default value or null if allowed.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40610566
Does the table have any other idexes on it?

mlmcc
0
 
LVL 3

Author Comment

by:colinspurs
ID: 40611905
Hi, thanks for your responses.  I haven't looked at this code for a long time and had forgotten that the duplicate trigger can apply to any unique index, and there is indeed a separate unique index.  And yes, I will remove the ID from the insert.
0
 
LVL 3

Author Closing Comment

by:colinspurs
ID: 40611906
Thanks very much.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now