Solved

mysql duplicate key problem

Posted on 2013-12-28
11
340 Views
Last Modified: 2014-01-07
that is my table http://prntscr.com/2el4in as you should see it hase an index


insert into switchStatus(switch,port,status,limit) values ('T01','1007','notPresent','0') on duplicate key update status='notPresent' limit='0';
insert into switchStatus(switch,port,status,limit) values ('T01','1007','notPresent','0') on duplicate key update status='notPresent' limit='0';
insert into switchStatus(switch,port,status,limit) values ('T01','1007','notPresent','0') on duplicate key update status='notPresent' limit='0';
insert into switchStatus(switch,port,status,limit) values ('T01','1007','notPresent','0') on duplicate key update status='notPresent' limit='0';



when i export this command 5 times it records 5 times :S

why it does not update depending on the switch & port index
0
Comment
Question by:3XLcom
  • 6
  • 4
11 Comments
 
LVL 11

Accepted Solution

by:
Technodweeb earned 500 total points
Comment Utility
You in deed have an index but what is not shown is the unique key which must be set on one of the fields on the table. The unique key is not required so if it is not necessary, remove its option.
0
 

Author Comment

by:3XLcom
Comment Utility
should you send it as sql script please
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
You would need to export the schema of your table and post it here first. The name of the key will be contained within but your screen caps do not show it. I will need that info before I could tell you anything specific.

Also, take this time to back up your datatables...
0
 

Author Comment

by:3XLcom
Comment Utility
it is attached
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
What I am asking for looks like this...
-- phpMyAdmin SQL Dump
-- version 4.0.8
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 28, 2013 at 08:20 AM
-- Server version: 5.1.72-cll
-- PHP Version: 5.3.17

SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `TABLENAME` (
  `AuditID` int(11) NOT NULL AUTO_INCREMENT,
  `DateAdded` datetime NOT NULL,
  `DateModified` datetime NOT NULL,
  `FirstName` varchar(255) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `EMCode` varchar(50) NOT NULL,
  `Phone` varchar(10) NOT NULL,
  `NML` varchar(25) NOT NULL,
  PRIMARY KEY (`AuditID`),
  UNIQUE KEY `AuditID` (`AuditID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;
SET FOREIGN_KEY_CHECKS=1;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
If you notice, line 32 defines one of my Index fields and Line 32 also defines an index field but with Unique status. This means that no values may be duplicated in this field.
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
Since I do not know your Unique Key entries names I will give you a very generic command to do this...

ALTER TABLE <TABLENAME> DROP INDEX <KEYNAME>

The <KEYNAME> may be the same as a column name but it can be totally different. YMMV...

!!! Make a backup before you try this !!!
0
 

Author Comment

by:3XLcom
Comment Utility
but this is not my table and i have 2 keys not one
0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
I will write the command but you will need to post the definition for the schema as I showed you in a prior comment. If this is not possible, you can take the generic command I have given you and modify it for your specific needs. Add your table name and add your unique key name.. . voila...

!!! BACKUP your tables first...
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
For better or worse, computer programming is an activity that requires meticulous attention to detail.  Let's try to deconstruct this a little bit.  You have shown us several identical versions of the same INSERT query.

insert into switchStatus (
  switch
, port
, status
, limit
) values (
  'T01'
, '1007'
, 'notPresent'
, '0'
) on duplicate key update 
status='notPresent' 
limit='0'
;

Open in new window

What this seems to be saying is that you want to INSERT data, but if you encounter a duplicate key, you want to UPDATE two columns.  If the query INSERTS data, it does so because it did not encounter a duplicate key.  Perhaps there is no key on the switch and/or port columns?  The screen shot does not show any keys.

Also, in the screen shot, there are columns named statuse and limite but no columns named status or limit.  The query should have failed if you attempted to INSERT or UPDATE columns that did not exist in the table.  You might want to check and see if you're using the right data and the right script.
0
 

Author Closing Comment

by:3XLcom
Comment Utility
sorry thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

18 Experts available now in Live!

Get 1:1 Help Now