Solved

ON DUPLICATE KEY UPDATE question

Posted on 2010-09-09
1
317 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
1 Comment
 
LVL 3

Accepted Solution

by:
SuchetaL earned 500 total points
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now