Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ON DUPLICATE KEY UPDATE with AUTO INCREMENT pk

Posted on 2015-02-14
4
Medium Priority
?
484 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 80

Accepted Solution

by:
arnold earned 2000 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 101

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

926 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