Solved

Overwrite existing data in MySQL Database

Posted on 2010-08-28
13
323 Views
Last Modified: 2012-05-10
I have a simple database.  The AccountName field is the 'Unique value' because I don't want it to duplicate.  Problem is, I will have a feed for AccountName 1234 today for RSLTS1, but tomorrow another feed that will be different for RSTLS1.  So, how might I make it overwrite the RSLTS1 = RSLTS16 contents?  

Basically, the record is created the first time for each new AccountName, but I want it to edit thereafter when a new transaction comes across.  I am using MIRTH to feed the database.  It is an HL7 interface engine which parses specific messages and puts contents into the database.  I created the database using PHPMYADMIN.  Is this enough information to resolve this issue?

The error message from MIRTH indicates that the AccountName is duplicated, so it doesn't try the other fields.  If I tell the database not to care if AccountName is unique, then it works just fine.  What more can I do to the database to allow the AccountName once, but not have the app error out.  Can I do something so that the entire line is overwritten (including the AccountName)?  This way, it would retain its uniqueness, but not have other programs error out.

Thanks


-- phpMyAdmin SQL Dump
-- version 3.3.0-beta1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 28, 2010 at 08:40 PM
-- Server version: 5.1.42
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!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 */;

--
-- Database: `IVCHL7`
--

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

--
-- Table structure for table `IVCiPhone`
--

CREATE TABLE IF NOT EXISTS `IVCiPhone` (
  `AccountName` varchar(255) NOT NULL,
  `Password` varchar(255) DEFAULT NULL,
  `RSLTS1` varchar(255) DEFAULT NULL,
  `RSLTS2` varchar(255) DEFAULT NULL,
  `RSLTS3` varchar(255) DEFAULT NULL,
  `RSLTS4` varchar(255) DEFAULT NULL,
  `RSLTS5` varchar(255) DEFAULT NULL,
  `RSLTS6` varchar(255) DEFAULT NULL,
  `RSLTS7` varchar(255) DEFAULT NULL,
  `RSLTS8` varchar(255) DEFAULT NULL,
  `RSLTS9` varchar(255) DEFAULT NULL,
  `RSLTS10` varchar(255) DEFAULT NULL,
  `RSLTS11` varchar(255) DEFAULT NULL,
  `RSLTS12` varchar(255) DEFAULT NULL,
  `RSLTS13` varchar(255) DEFAULT NULL,
  `RSLTS14` varchar(255) DEFAULT NULL,
  `RSLTS15` varchar(255) DEFAULT NULL,
  `RSLTS16` varchar(255) DEFAULT NULL,
  UNIQUE KEY `AccountName` (`AccountName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `IVCiPhone`
--

Open in new window

0
Comment
Question by:weklica
[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
  • 7
  • 6
13 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 33551540
Use an Update query to replace RSLTS1 = RSLTS16.
0
 

Author Comment

by:weklica
ID: 33551550
I am pretty new to this.  Is this a database change for each f those fields or how the program dumps the data?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 33551554
Sample below... I got tired of typing but it's the general idea.
UPDATE IVCiPhone SET `RSLTS1`= 'Newdat1',`RSLTS2`= 'Newdat2',`RSLTS3`= 'Newdat3',`RSLTS4`= 'Newdat4',`RSLTS5`= 'Newdat5', .... `RSLTS16`= 'Newdat16' WHERE `AccountName` = 'theaccount';

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 33551558
I don't understand your question.  What you posted above is a dump showing only the table structure.
0
 

Author Comment

by:weklica
ID: 33551596
Ya, just dumped it cause I didn't know if it would help indicate if the issue was with the table or something.  I will see if there is a way I can do this with MIRTH which is what feeds the database.  
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 33551645
Ok.  A 'SELECT' query returns info from a table in the database, a 'INSERT' query puts a new record in a table, and an 'UPDATE' changes the info in an existing record.
0
 

Author Closing Comment

by:weklica
ID: 33551727
Thanks
0
 

Author Comment

by:weklica
ID: 33551984
I have a bit of confusion as I try this.  Your code snippet above indicated to update the rslts fields.  How does it know which rslts fields?  I wouls need to have it update reslults fields for a given accountname
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 33551994
That's the WHERE part at the end of that line.
0
 

Author Comment

by:weklica
ID: 33552040
Sorry to keep bothering about this.  i am searching web forums.  Keep putting incorrect Syntax down.  What about this:  (see code snippet)

Not quiet right clearly.  
UPDATE IVCiPhone  	
SET RSLTS2 = 	( SELECT testing}
FROM IVCiPhone
WHERE AccountName = 1234-558)

Open in new window

0
 

Author Comment

by:weklica
ID: 33552041
OOPs, on last snippet, remove the curly bracket.  Didn't mean to have it after the updated information which is testing
0
 

Author Comment

by:weklica
ID: 33552060
NEVERMIND.:

THIS WAS IT:

UPDATE IVCiPhone        
SET RSLTS2=${RSLTS2}
WHERE AccountName=${AccountNumber}
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 33552191
Sorry, didn't mean to ignore you.  You seem to have figured it out.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Distinct Question 3 15
Using datetime as triggers 2 27
Running Total Using new MS SQL Function 21 53
Specify timing interval fro change data 2 57
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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