update a table - 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

General SQL Server Forums

New to SQL Server Programming

update a table

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-30 : 18:34:46

Hi All,
I have 3 tables and their fields:

IMeter- SPID, STypeName
SPoint- SPID, STypeID
SType-STypeID,STypeName

Now, I want to update the values of STypeID in SPoint table based on the values of STtypeID in SType table.

What I mean is:
Get the values of SPID and STypeName from IMeter. Now based on this STypeName, get the corresponding STypeID from SType table. Finally, in the SPoint table, for every SPID update STypeID value :by creating a join with SPID of IMeter , fetch the STypeName from IMeter, then based on this STypeName, fetch STypeID from SType table. Finally, use this STypeID to update STypeID of SPoint table.

Thanks in anticipation,
Mavericky

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-10-01 : 02:27:22

[code]
update sp
set sp.STypeID = st.STypeID

from IMeter as im
join SPoint as sp
on im.spid = sp.spid
join SType as st
on st.STypeID = sp.STypeID

--or

update sp
set sp.STypeID = st.STypeID

from SPoint as sp
join SType as st
on st.STypeID = sp.STypeID

[/code]
and you don't need to join IMeter table at all, if i'm understanding your explanation correctly.

best

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-01 : 06:33:27

Hope following will work for you
update SPoint
set SPoint.STypeID = SType.STypeID
from SPoint
inner join SType
on SType.STypeID = SPoint.STypeID

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

mavericky
Posting Yak Master

117 Posts

Posted - 2011-10-01 : 07:52:30

quote:
Originally posted by jassi.singh

Hope following will work for you
update SPoint
set SPoint.STypeID = SType.STypeID
from SPoint
inner join SType
on SType.STypeID = SPoint.STypeID

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

mavericky
Posting Yak Master

117 Posts

Posted - 2011-10-01 : 07:53:35

Thanks a lot everybody!!Yup that helps. It solved my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources