Notifications
Clear all
Topic starter
I need to changean obs name for an obs term. This requires updating the old HDID to the new desired HDID. I would initially copy the Obs table to Obs_Bak, then execute the SQL code against the Obs Table. The code is ACCESS SQL would be:
UPDATE dbo_OBS SET dbo_OBS.HDID = [New HDID]
WHERE (((dbo_OBS.HDID)=[Old HDID]));
Any comment? Am I being naïve?
Posted : February 3, 2016 9:41 am
Two things come to mind:
1) How will the RPTOBS and LASTLABS tables (views) be impacted by this?
2) GE's reaction to changing data at the db level - I have seen stern warnings from GE before.
Posted : February 3, 2016 9:57 am
I have done this multiple times without any issues. Also, just an observation, but why so many parentheses in your Where Clause?
Posted : February 3, 2016 9:58 am
I wouldn't make a backup of the entire obs table I would just select into a custom table the obs rows I'm going to change.
Below is what I'm thinking off the top of my head. Note I didn't test myself. I would always keep in my mind like joeg1962 said that GE isn't hot about this since its so easy to screw things up big time. Also remember that this won't effect any text translation of documents just the obs.
One more thing I've developed the habit of always running in a transaction and rolling back. That way I can make sure the results are as expected before committing. You can even throw a select statement at the bottom before rolling back.
One more thing when you run it run it all at once with the rollback or commit. Don't leave the tran open or those tables will become locked and CPS will most likely lock up.
BEGIN TRAN;
--Backup before change
SELECT *
INTO cus_obs_ProjectNameHERE_DATEHERE
FROM OBS
WHERE hdid = Old HDID;
--Update obs
UPDATE OBS
SET HDID = [New HDID]
WHERE HDID = [Old HDID];
--Rollback and check changed rows as expected then run again and commit
--COMMIT TRAN;
ROLLBACK TRAN;
Posted : February 3, 2016 6:49 pm
I know this is an old thread but I thought I would add my comments in case someone references this in the future.
I agree with Bovie, if you are on CPS (MSSQL), make sure to run it as a transaction and make sure that the number of rows updated matches what you expect. On Oracle this should be automatic, just make sure you check the data before running the commit.
I usually export my old data to a CSV before making a change such as this. I save the CSV data, the query I ran along with any notes on it in a folder incase I need to reference it again.
The main issues I have run into is:
CCDA's sometimes show the date you updated the record as the end date in a high/low date range. I believe this to be a bug in the CCDA generation process. On outbound CCDA interfaces I end up changing my Results and General Obs sections so that my end date is replaced by a copy of my start date.
If you have an outbound OBS interface that is not filtered, it will trigger an outbound HL7 message for each updated OBS. I updated about 15k records one time and it tied up our DTS/LL server for an hour plus. We no longer have an outbound OBS interface because it was replaced with a subscription based interface. (We were using it for immunization reporting)
Posted : December 5, 2016 3:53 am
