Solved

ON DUPLICATE KEY UPDATE with AUTO INCREMENT pk

Posted on 2015-02-14
4
449 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

772 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