Table Update 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 2000 Forums

Transact-SQL (2000)

Table Update Help

Author

Topic

kevinq
Starting Member

3 Posts

Posted - 2003-05-15 : 05:31:41

Hi,
I have a table which stores codes, the table has a seperate primary key.

Eg.
Class Table
class_id, class_code

I have another main table which is related to this table but the problem is that it is related based on the class code and not the class_id.

Eg.
Main Table
library_id,library_name,class_code

Main table changed to:
library_id,library_name,class_code,class_id

I have added a field, class_id, to the main table and plan to import the corresponding values for the class_id into the main table and then remove the class_code field.

Does anyone know if there is any way this could be done using transact sql.

Any help would be much appreciated.

Thanks.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-15 : 07:06:07

Well, the simple answer is ...


update [main table]
set class_code = c.class_code
from [main table] m inner join [class table] c
     on m.class_id = c.class_id

The correct answer is ...
Normalize your data tier. Your current model can lead to data integrity issues. Because of the redundancy, the data might become inconsistent, with different records showing different a class_code for the same class_id.

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 16:43:16

Seems I remeber these same examples from other posts...

was that last semester?

Schools almost out. Bet it's part of a final.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources