Update Table Assistance Needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Table Assistance Needed

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-22 : 17:45:45

Hello,
I am trying to update a table based on values that I have imported into a temporary table. Basically I have a list of lab codes (EMR_temp_labtest_configupdate) and each lab has a zseg code tied to it. The definitions for zseg code are in a separate table called (EMR_zseg_code_descriptions)

I need to update the lab_test_add_conf to add in each lab code that does not have any configuration information (not exists in lab_test_add_conf) based on the zsegcode.

For example a zsegcode (defined in the emr_zseg_code_descriptions table) is ZBL and the lab code 003277 fits into the zseg category according to the temp table. For each lab code that first into the ZBL category a row needs to be inserted for

Example of table data:

emr_temp_labtest_config

labtestcode, zsegcode
003277, ZBL

emr_zseg_code_descriptions
zsegcode, valuecode
ZBL, PATRAC
ZBL, HERITG

I want to look at the data in the temp table and determine which category it is in and then insert into the lab_test_add_conf table a row for each lab test each zseg table value code that exists.

My Final Goal:
lab_test_add_conf:

lab test code, valuecode
003277, PATRAC
003277, HERITG

I know I need to do an update statement but I am not sure how to set up the SET statement or if there is anythign that I need to take into consideration. Here is my code so far.....any thoughts?

select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists
(Select *
From lab_test_add_conf b
where a.labtestcode = b.labtest_key)

update table lab_test_add_conf

select a.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,
b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-22 : 17:50:53

Or could I run a query and get what I want combined into a temporary table and then do an insert into clause to get all the rows into the table that do not exist yet?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 02:03:52

If i understand your explanation correctly you will have only 1 record in your lab_test_add_conf table for 003277 so that updating will cause it hold only one of two valuecode values (PATRAC,HERITG). So i think what you are looking at is to get the second value also as a seperate record.Can you confirm if this is your requirement?

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 10:51:47

Actually there will be as many records as the Zseg table holds. So since 003277 is coded as Zseg ZBL there needs to be an entry for each value code that ZBL has which (PATRAC,HERITG). There will be two rows entered.

I posted another post with the subquery I am creating to combine the data together called Sub Query Problem. I am going to combine it all together and then just insert it all into the table with an insert into statement. I am hoping that will work....

quote:
Originally posted by visakh16

If i understand your explanation correctly you will have only 1 record in your lab_test_add_conf table for 003277 so that updating will cause it hold only one of two valuecode values (PATRAC,HERITG). So i think what you are looking at is to get the second value also as a seperate record.Can you confirm if this is your requirement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources