Update Table Help - SQL Server Forums

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 Help

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-27 : 11:04:12

Hello,

I have an existing table that many fields are missing some information that must be obtained by comparing a code to another existing table and then updating the main table.

Here is my code to determine which lab tests are missing table_name and field_name data.

select distinct a.*
from lab_test_add_conf a
join emr_temp_labtest_configupdate b on a.labtest_key = b.labtestcode
join emr_zseg_code_descriptions c on b.zsegcode = c.zsegcode
where a.table_name = ' ' and a.field_name = ' '

The emr_zseg_code_descriptions table contains the actual data that must be inserted into the lab_test_add_conf columns for field_name and table_name joined on zsegcode. I have never set up an update statement that has to deal with joins. Does anyone have a good way to set this up??

Thanks! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 11:07:11

[code]update a
set a.table_name=c.table_name,
a.field_name=c.field_name
from lab_test_add_conf a
join emr_temp_labtest_configupdate b on a.labtest_key = b.labtestcode
join emr_zseg_code_descriptions c on b.zsegcode = c.zsegcode
where a.table_name = ' ' and a.field_name = ' '[/code]

I'm assuming field names are same in emr_zseg_code_descriptions & lab_test_add_conf tables

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-27 : 11:11:33

As always you are so helpful!! :) Thanks so much. :)

quote:
Originally posted by visakh16

update a
set a.table_name=c.table_name,
a.field_name=c.field_name
from lab_test_add_conf a
join emr_temp_labtest_configupdate b on a.labtest_key = b.labtestcode
join emr_zseg_code_descriptions c on b.zsegcode = c.zsegcode
where a.table_name = ' ' and a.field_name = ' '

I'm assuming field names are same in emr_zseg_code_descriptions & lab_test_add_conf tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources