Updating several fields in one table.

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 several fields in one table.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-15 : 13:55:49

I'm trying to update 3 fields in one table with a query from another table. This is as far as I got. Here is the query which returns the values:

(SELECT fprodcl,
       [m2mdata01].[dbo].soitem.fpartno,
       fgroup,
       m2mdata01.dbo.sorels.[identity_column]
  FROM    [m2mdata01].[dbo].soitem
       INNER JOIN
          [m2mdata01].[dbo].sorels
       ON m2mdata01.dbo.soitem.[fsono] = m2mdata01.dbo.sorels.[fsono]
      AND m2mdata01.dbo.soitem.[finumber] = m2mdata01.dbo.sorels.[finumber]) as M2M

This is the portion of the Update statement I have:

Update [CheltonCustomizations].[dbo].[soMods]
set prodcl = m2m.fprodcl,
		fpartno = M2M.fpartno,
		fgroup = M2M.fgroup

Somods relates to the sub query because SoMods.fSOKey = M2M.identity_column.

Can someone point me in the right direction?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 13:59:25

[code]Update s
set s.prodcl = m2m.fprodcl,
s.fpartno = M2M.fpartno,
s.fgroup = M2M.fgroup
from [CheltonCustomizations].[dbo].[soMods] s
join (SELECT fprodcl,
[m2mdata01].[dbo].soitem.fpartno,
fgroup,
m2mdata01.dbo.sorels.[identity_column]
FROM [m2mdata01].[dbo].soitem
INNER JOIN
[m2mdata01].[dbo].sorels
ON m2mdata01.dbo.soitem.[fsono] = m2mdata01.dbo.sorels.[fsono]
AND m2mdata01.dbo.soitem.[finumber] = m2mdata01.dbo.sorels.[finumber]) as M2M
ON M2M.[identity_column]=s.fSOKey[/code]

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-15 : 14:56:13

Thanks visakh. I appreciate it as always.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources