<< Click to Display Table of Contents >> Navigation:  Database > Modifications > Update Existing CAS_RN Values

•Analytes Referenced ONLY in DT_RESULT

•Analytes Referenced in BOTH DT_RESULT and DT_RESULT_QC

 

An EQuIS database stores analyte information in the RT_ANALYTE reference table, typically using the CAS_RN (Chemical Abstracts Service Registry Number) field to reference them. The relationships between tables can make changing a CAS_RN difficult for existing results. Several methods for updating CAS_RNs are described below.

 

Analytes Referenced ONLY in DT_RESULT

 

If your CAS_RN is only referenced in DT_RESULT, and not DT_RESULT_QC, then updating a CAS_RN is relatively simple.

1.Open reference table RT_ANALYTE.

2.Add a record with the new CAS_RN details and save the table.

3.Select the CAS_RN to be changed.

4.Use the Find All Child Records option on the Data Grid toolbar to find the records that need to be changed.

5.Change all child records accordingly in the associated tables.

6.Save.

7.After completing all the changes, delete the original, incorrect CAS_RN.

 

Analytes Referenced in BOTH DT_RESULT and DT_RESULT_QC

 

Because EQuIS uses CAS_RN values as PRIMARY keys in both DT_RESULT and DT_RESULT_QC, it is not possible to make changes to the CAS_RN of a result that has been referenced in both tables. Attempting to do so will result in a foreign key constraint error.

 

In order to amend records that are referenced in both tables, a database administrator can run a script in SQL Server Management Studio, replacing the CAS_RN values accordingly:

 

Before running any scripts, we highly recommend creating a backup of the EQuIS database.

 

This script will update the values in DT_RESULT, DT_RESULT_QC and RT_MTH_ANL_GROUP_MEMBER. Follow the steps below.

 

1.Back up the EQuIS database.

2.Use the Find All Child Records option on the Data Grid toolbar to find the tables that need to be changed.

3.In SQL Server Management Studio, right-click on the database in question and select 'New Query'.

4.Paste the script below into the query window.  

5.Update the @OLD_CAS_RN and the @NEW_CAS_RN values to match your values, with @OLD_CAS_RN being the incorrect CAS_RN, and @NEW_CAS_RN. The values that need to be changed are within the '  '.

6.Run the script against the database.

7.In EQuIS, manually change the CAS_RN values in any other tables as needed.

8.The script will not delete the original CAS_RN from RT_ANALYTE. If that record should be removed, it can be done manually by deleting the record from the table.

9.Once the changes have been made, use Find All Child Records to check that everything has been changed as required.

 

 

declare @old_cas_rn as varchar(15) = '100-41-4444' -- This is the cas-rn you want to replace
declare @new_cas_rn as varchar(15) = '100-41-4' --This is the cas-rn you want to insert
declare @new_chem_name as varchar(255) = 'Ethylbenzene' --This is the chemical name of the new cas-rn.  Comment out if already added.
-- Step one, add the new cas_rn to rt_analyte
insert into rt_analyte (cas_rn, chemical_name,status_flag) -- Comment out if already added
values (@new_cas_rn,@new_chem_name,'A') --Comment out if already added.
-- Step two, create row copies using new cas_rn
insert into dt_result(facility_id, test_id, cas_rn, result_text, result_numeric, result_error_delta, result_type_code, stat_result, reportable_result, detect_flag, lab_qualifiers, validator_qualifiers, approval_code, interpreted_qualifiers, dqm_qualifiers, approval_a, approval_b, approval_c, approval_d, hold_time_status, method_detection_limit, reporting_detection_limit, quantitation_limit, result_unit, detection_limit_unit, tic_retention_time, custom_field_1, custom_field_2, custom_field_3, remark, dqm_remark, desorb_efficiency, value_type, stat_type, custom_field_4, custom_field_5, validated_yn, ebatch, uncertainty, minimum_detectable_conc, counting_error, critical_value)
select facility_id, test_id, @new_cas_rn, result_text, result_numeric, result_error_delta, result_type_code, stat_result, reportable_result, detect_flag, lab_qualifiers, validator_qualifiers, approval_code, interpreted_qualifiers, dqm_qualifiers, approval_a, approval_b, approval_c, approval_d, hold_time_status, method_detection_limit, reporting_detection_limit, quantitation_limit, result_unit, detection_limit_unit, tic_retention_time, custom_field_1, custom_field_2, custom_field_3, remark, dqm_remark, desorb_efficiency, value_type, stat_type, custom_field_4, custom_field_5, validated_yn, ebatch, uncertainty, minimum_detectable_conc, counting_error, critical_value
from dt_result where cas_rn = @old_cas_rn
-- Step three, update dt_result_qc and rt_mth_anl_group_member with new cas_rn values
update dt_result_qc set cas_rn = @new_cas_rn where cas_rn = @old_cas_rn
update rt_mth_anl_group_member set cas_rn = @new_cas_rn where cas_rn = @old_cas_rn
-- Step four, delete old rows from dt_result
delete from dt_result where cas_rn = @old_cas_rn