Solved

mysql duplicate key problem

Posted on 2013-12-28
11
351 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
[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
  • 6
  • 4
11 Comments
 
LVL 11

Accepted Solution

by:
Gregory Miller earned 500 total points
ID: 39743522
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
ID: 39743524
should you send it as sql script please
0
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39743531
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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

by:3XLcom
ID: 39743534
it is attached
0
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39743556
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
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39743559
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:Gregory Miller
ID: 39743572
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
ID: 39743575
but this is not my table and i have 2 keys not one
0
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39743578
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 110

Expert Comment

by:Ray Paseur
ID: 39743670
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
ID: 39762530
sorry thank you
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

707 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