• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Overwrite existing data in MySQL Database

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
weklica
Asked:
weklica
  • 7
  • 6
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
Use an Update query to replace RSLTS1 = RSLTS16.
0
 
weklicaAuthor Commented:
I am pretty new to this.  Is this a database change for each f those fields or how the program dumps the data?
0
 
Dave BaldwinFixer of ProblemsCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dave BaldwinFixer of ProblemsCommented:
I don't understand your question.  What you posted above is a dump showing only the table structure.
0
 
weklicaAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
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
 
weklicaAuthor Commented:
Thanks
0
 
weklicaAuthor Commented:
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
 
Dave BaldwinFixer of ProblemsCommented:
That's the WHERE part at the end of that line.
0
 
weklicaAuthor Commented:
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
 
weklicaAuthor Commented:
OOPs, on last snippet, remove the curly bracket.  Didn't mean to have it after the updated information which is testing
0
 
weklicaAuthor Commented:
NEVERMIND.:

THIS WAS IT:

UPDATE IVCiPhone        
SET RSLTS2=${RSLTS2}
WHERE AccountName=${AccountNumber}
0
 
Dave BaldwinFixer of ProblemsCommented:
Sorry, didn't mean to ignore you.  You seem to have figured it out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now