Updating the table from another tables value[RESOL

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

General SQL Server Forums

New to SQL Server Programming

Updating the table from another tables value[RESOL

Author

Topic

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 13:03:40

I have two tables master and comp table. In master table, I have columns called LB1, LB2, LB1_ID, LB2_ID and in my comp table I have ID and CompName. I need to update my master table the following way

If LB1 in master table has a value of 'XYZ' then I need to find that value in Comp Table in column compName and then put the ID from comp table to master table in LB1

master

LB1 LB2 LB1_ID LB2_ID
XYZ HIJ
ABC KLM
DEF RAW
PQR VQS

Comp

ID CompName
1 XYZ
2 ABC
3 RAW
4 DEF
5 VQS
6 HIJ
7 KLM
8 PQR

I need to poulate my master table like this

master

LB1 LB2 LB1_ID LB2_ID
XYZ HIJ 1 6
ABC KLM 2 7
DEF RAW 4 3
PQR VQS 8 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 13:09:05

[code]select m.*,c1.ID,c2.ID
from master m
inner join comp c1
on c1.CompName = m.LB1
inner join comp c2
on c2.CompName = m.LB2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 13:15:14

I need to do the update of the master table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 13:16:55

just change it to an update


update m
set m.LB1_ID =c1.ID,
m.LB2_ID = c2.ID
FROM master m
inner join comp c1
on c1.CompName = m.LB1
inner join comp c2
on c2.CompName = m.LB2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 13:31:47

Thanks Visakh.

anjali66
Starting Member

23 Posts

Posted - 2011-01-12 : 14:03:15

After executing the update query in my master table, I still see NULL records in LB1_ID , LB2_ID, although there is value in LB1, LB2.

I am not sure why this is happening.

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:16:16

may be only one of them have data not both. try like


update m
set m.LB1_ID =COALESCE(c1.ID,m.LB1_ID),
m.LB2_ID = COALESCE(c2.ID,m.LB2_ID)
FROM master m
left join comp c1
on c1.CompName = m.LB1
left join comp c2
on c2.CompName = m.LB2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources