Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

mysql duplicate key problem

Posted on 2013-12-28
11
Medium Priority
?
355 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 12

Accepted Solution

by:
Gregory Miller earned 2000 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 12

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

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

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 12

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 12

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 12

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 111

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

604 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