<< 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

•Update Values in 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:

 

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
-- Step one, 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 two, update dt_result_qc with new car_rn values
update dt_result_qc set cas_rn = @new_cas_rn where cas_rn = @old_cas_rn
-- Step three, delete old rows from dt_result
delete from dt_result where cas_rn = @old_cas_rn

 

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

 

Update Values in DT_RESULT and DT_RESULT_QC

 

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

 

1.Back up the EQuIS database.

2.Add a new record to RT_ANALYTE with the updated CAS_RN.

8.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 being the one just added to RT_ANALYTE. 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 (such as RT_MTH_ANL_GROUP_MEMBER) as needed.

8.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_an_group_member with new car_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