Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ON DUPLICATE KEY UPDATE question

Posted on 2010-09-09
1
Medium Priority
?
323 Views
Last Modified: 2012-06-27
Got the tables defined below. I need to only update values when a record is trying to be inserted if it is still in the table.
However, I am not sure how to build the indexes for the ON DUPLICATE KEY UPDATE because the keys are mostly autoinc.
Can anyone tell me how to configure these tables to allow this?

Does every column need to be in the key? Does every column need to be updated?

CREATE TABLE IF NOT EXISTS `niin` (
  `NIIN_ID` int(10) NOT NULL AUTO_INCREMENT,
  `NIINS_ID` int(10) NOT NULL DEFAULT '0',
  `NSN_NUMBER` varchar(255) NOT NULL DEFAULT '0',
  `ITEMNAME` varchar(255) NOT NULL DEFAULT '0',
  `SCHEDULEB` varchar(255) NOT NULL DEFAULT '0',
  `ENAC` varchar(255) NOT NULL DEFAULT '0',
  `IMAGE` varchar(255) NOT NULL DEFAULT '',
  KEY `Index 1` (`NIIN_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `niinid` (
  `NIINID_ID` int(10) NOT NULL AUTO_INCREMENT,
  `NIIN_ID` int(10) NOT NULL DEFAULT '0',
  `FIIG` varchar(20) NOT NULL DEFAULT '0',
  `INC` varchar(20) NOT NULL DEFAULT '0',
  `CRITCD` varchar(20) NOT NULL DEFAULT '0',
  `II` varchar(20) NOT NULL DEFAULT '0',
  `RPDMRC` varchar(20) NOT NULL DEFAULT '0',
  `DMIL` varchar(20) NOT NULL DEFAULT '0',
  `DMILINTCD` varchar(20) NOT NULL DEFAULT '0',
  `NIINASGMT` varchar(20) NOT NULL DEFAULT '0',
  `PMIC` varchar(20) NOT NULL DEFAULT '0',
  `ADP` varchar(20) NOT NULL DEFAULT '0',
  `ESDEMI` varchar(20) NOT NULL DEFAULT '0',
  `HMIC` varchar(20) NOT NULL DEFAULT '0',
  `HCC` varchar(20) NOT NULL DEFAULT '0',
  KEY `Index 1` (`NIINID_ID`),
  KEY `Index 2` (`NIIN_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `niinprocurement` (
  `NIINPROCUREMENT_ID` int(10) NOT NULL AUTO_INCREMENT,
  `NIIN_ID` int(10) NOT NULL DEFAULT '0',
  `EFF_DT` varchar(20) NOT NULL DEFAULT '0',
  `MOE` varchar(20) NOT NULL DEFAULT '0',
  `AAC` varchar(20) NOT NULL DEFAULT '0',
  `SOS` varchar(20) NOT NULL DEFAULT '0',
  `UI` varchar(20) NOT NULL DEFAULT '0',
  `UI_PRICE` decimal(10,2) NOT NULL DEFAULT '0.00',
  `QUP` varchar(20) NOT NULL DEFAULT '0',
  `CIIC` varchar(20) NOT NULL DEFAULT '0',
  `SLC` varchar(20) NOT NULL DEFAULT '0',
  `REP` varchar(20) NOT NULL DEFAULT '0',
  `USC` varchar(20) NOT NULL DEFAULT '0',
  KEY `Index 1` (`NIINPROCUREMENT_ID`),
  KEY `Index 2` (`NIIN_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `niins` (
  `NIINS_ID` int(10) NOT NULL AUTO_INCREMENT,
  `PARTNO` varchar(250) NOT NULL DEFAULT '',
  `DESCRIPTION` varchar(255) NOT NULL,
  `NSN` varchar(255) NOT NULL,
  KEY `Index 1` (`NIINS_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `niinxref` (
  `NIINXREF_ID` int(10) NOT NULL AUTO_INCREMENT,
  `NIIN_ID` int(10) NOT NULL DEFAULT '0',
  `REFPN` varchar(20) NOT NULL DEFAULT '0',
  `CAGECD` varchar(20) NOT NULL DEFAULT '0',
  `STAT` varchar(20) NOT NULL DEFAULT '0',
  `RNCC` varchar(20) NOT NULL DEFAULT '0',
  `RNVC` varchar(20) NOT NULL DEFAULT '0',
  `DAC` varchar(20) NOT NULL DEFAULT '0',
  `RNAAC` varchar(20) NOT NULL DEFAULT '0',
  `RNFC` varchar(20) NOT NULL DEFAULT '0',
  `RNSC` varchar(20) NOT NULL DEFAULT '0',
  `RNJC` varchar(20) NOT NULL DEFAULT '0',
  `SADC` varchar(20) NOT NULL DEFAULT '0',
  `HCC` varchar(20) NOT NULL DEFAULT '0',
  `MSDS` varchar(20) NOT NULL DEFAULT '0',
  KEY `Index 1` (`NIINXREF_ID`),
  KEY `Index 2` (`NIIN_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window

0
Comment
Question by:EddieShipman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 3

Accepted Solution

by:
SuchetaL earned 2000 total points
ID: 33643703
Hi Eddie,
I need to only update values when a record is trying to be inserted if it is still in the table.
How do you decide that this is a repeated record? Other than the auto inc ID's there must be something that you don't want repeated. For the first table in your example "niin" - assume you don't want the NSN_NUMBER & ITEMNAME repeated. So add it as a Unique Index,
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.
 In case you want to check with the last inserted record for the auto incremented ID - you can do the following
// Your table has cols - id, a, b, c - then
INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

Hope that helps
Sucheta
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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
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 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

670 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