Solved

ON DUPLICATE KEY UPDATE question

Posted on 2010-09-09
1
318 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
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Limit on number of values in mysql in clause 3 53
understanding output of mysql version 2 50
MySQL Init Waits 25 78
mcrypt_create_iv() is deprecated 4 69
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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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

16 Experts available now in Live!

Get 1:1 Help Now