how to combine two tables - 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

SQL Server Development (2000)

how to combine two tables

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-04-20 : 05:18:58

Hello,

I have One table having (BusList) SchoolID,ClassID,RegNo,sClass as fields . Another table having(SchoolClass) ClassID,SClass,SchoolID as fields

Now my problem is, I have to update Buslist table's ClassID field which is having null(all are null) values with the ClassID field of Bus Table.

I mean

I have the following values in Buslist table

BusList
---------
SchoolID ClassID RegNo SClass

IHS Null 1000 1-A
IHS Null 1001 1-A
IHS Null 1002 KG1-A
IHS Null 1003 1-A
BRT Null 1002 KG1-A
BRT Null 1003 1-A

SchoolClass
-----------

ClassID SClass SchoolID

1 1-A IHS
2 KG1-A IHS
1 1-A BRT
2 KG1-A BRT

So, I have to update BusList as

SchoolID ClassID RegNo SClass

IHS 1 1000 1-A
IHS 1 1001 1-A
IHS 2 1002 KG1-A
IHS 1 1003 1-A
BRT 2 1002 KG1-A
BRT 1 1003 1-A

Any body having any suggestion

Thank you so much
Ceema

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-20 : 05:23:50

Update B set CalssId=S.CalssId
from BusList B inner join SchoolClass S
on B.SClass=S.SClass

Madhivanan

Failing to plan is Planning to fail