Solved

mysql duplicate key problem

Posted on 2013-12-28
11
345 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
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:Technodweeb
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
 

Author Comment

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

Expert Comment

by:Technodweeb
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 11

Expert Comment

by:Technodweeb
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:Technodweeb
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:Technodweeb
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 108

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

19 Experts available now in Live!

Get 1:1 Help Now