求一条救命的SQL语句~~~

[LBreathy]

各位大侠救救小弟吧，项目的数据库在客户那边已经部署上了，数据库里面有数据了，可是Person表的OID的数据插入错了，和部门表没有关系（部门信息是通过视图查出来的）Person表的OID应该和视图中OrgID绑定，但是现在Person表的OID中的数据是部门表的自增ID，部门表的自增ID是在修改和新增的时候ID都自增1（也就是说对部门信息进行修改就在部门表中增加一个新的记录），这样以来Person的OID就和部门信息对不上号了，现在要把Person表中的OID改为正确的OrgID，不是自增ID，该怎么办啊？要用SQL语句，请大侠帮帮我吧~~~~

[hao1hao2hao3]

还是贴一下表的结构吧！

这样描述太乱了。

[claro]

我看成致命的SQL语句，所以进来了。
对不起，我现在出去。

[LBreathy]

[Quote=引用 1 楼 hao1hao2hao3 的回复:]
还是贴一下表的结构吧！

这样描述太乱了。
[/Quote]


Person的表结构是：
ID 主键，
OID 单位编号

单位的信息是视图

[dawugui]

最好给出完整的表结构,测试数据,计算方法和正确结果.

发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

[LBreathy]

这个是Person表

这些是和视图有关系的表：
Certificate表：
Contacts表：

Organizations表：

User 表：

Catalog表：

Agreements 表：



一下是视图的SQL语句：

SELECT     a.ID, a.OrgID, a.OrgCode, a.SystemTypeId, a.EconomyTypeId, a.IndustryId, a.OrgDistId, a.OrgContactId, a.LinkMan1ContactId, a.LinkMan2ContactId, 
                      a.LegalPersonContactId, a.AgreementId, a.CertificateId, a.EmployeesNumber, a.RecEnabled, a.Deleted, a.TimeStamp, a.RecUserId, 
                      b.Name AS OrgName, b.Description AS OrgDescription, b.Address AS OrgAddress, b.Zipcode AS OrgZipcode, b.Telephone AS OrgTelephone, 
                      b.MobilePhone AS OrgMobilePhone, b.QQ AS OrgQQ, b.EMail AS OrgEMail, b.Fax AS OrgFax, c.Name AS LegalPersonName, 
                      c.Description AS LegalPersonDescription, c.Address AS LegalPersonAddress, c.Zipcode AS LegalPersonZipcode, 
                      c.Telephone AS LegalPersonTelephone, c.MobilePhone AS LegalPersonMobilePhone, c.QQ AS LegalPersonQQ, c.EMail AS LegalPersonEMail, 
                      c.Fax AS LegalPersonFax, d.Name AS LinkMan1Name, d.Description AS LinkMan1Description, d.Address AS LinkMan1Address, 
                      d.Zipcode AS LinkMan1Zipcode, d.Telephone AS LinkMan1Telephone, d.MobilePhone AS LinkMan1MobilePhone, d.QQ AS LinkMan1QQ, 
                      d.EMail AS LinkMan1EMail, d.Fax AS LinkMan1Fax, e.Name AS LinkMan2Name, e.Description AS LinkMan2Description, 
                      e.Address AS LinkMan2Address, e.Zipcode AS LinkMan2Zipcode, e.Telephone AS LinkMan2Telephone, e.MobilePhone AS LinkMan2MobilePhone, 
                      e.QQ AS LinkMan2QQ, e.EMail AS LinkMan2EMail, e.Fax AS LinkMan2Fax, f.CatalogName AS SystemTypeName, f.Remark AS SystemTypeRemark, 
                      g.CatalogName AS EconomyTypeName, g.Remark AS EconomyTypeRemark, h.CatalogName AS IndustryName, h.Remark AS IndustryRemark, 
                      i.CatalogName AS OrgDistName, i.Remark AS OrgDistRemark, j.AgreementCode, j.ValidDateBegin AS AgreementValidDateBegin, 
                      j.ValidDateEnd AS AgreementValidDateEnd, k.CertificateCode, k.ReleaseDate AS CertificateReleaseDate, k.ValidDateBegin AS CertificateValidDateBegin,
                       k.ValidDateEnd AS CertificateValidDateEnd, l.LoginName AS RecUserLoginName, l.Name AS RecUserName
FROM         dbo.Comm_Organizations AS a INNER JOIN
                          (SELECT DISTINCT OrgID, MAX(TimeStamp) AS TimeStamp
                            FROM          dbo.Comm_Organizations
                            GROUP BY OrgID) AS x ON a.OrgID = x.OrgID AND a.TimeStamp = x.TimeStamp LEFT OUTER JOIN
                      dbo.Comm_Contacts AS b ON a.OrgContactId = b.ID LEFT OUTER JOIN
                      dbo.Comm_Contacts AS c ON a.LegalPersonContactId = c.ID LEFT OUTER JOIN
                      dbo.Comm_Contacts AS d ON a.LinkMan1ContactId = d.ID LEFT OUTER JOIN
                      dbo.Comm_Contacts AS e ON a.LinkMan2ContactId = e.ID LEFT OUTER JOIN
                      dbo.Es_Catalog AS f ON a.SystemTypeId = f.CatalogID LEFT OUTER JOIN
                      dbo.Es_Catalog AS g ON a.EconomyTypeId = g.CatalogID LEFT OUTER JOIN
                      dbo.Es_Catalog AS h ON a.IndustryId = h.CatalogID LEFT OUTER JOIN
                      dbo.Es_Catalog AS i ON a.OrgDistId = i.CatalogID LEFT OUTER JOIN
                      dbo.Comm_Agreements AS j ON a.AgreementId = j.ID LEFT OUTER JOIN
                      dbo.Comm_Certificates AS k ON a.CertificateId = k.ID LEFT OUTER JOIN
                      dbo.Ts_User AS l ON a.RecUserId = l.UserID

SQL语句是SQL 2008自动生成的！请各位大侠帮帮小弟吧！

[duanzhi1984]

大概看了下，就是你的PERSON的OID(组织编号）更新错误了。。。


你是否能找到人员对应的组织编号。。。

若能找到，就

update Person SET Oid=ORGID
FROM 人员信息表 where pseron.id=人员信息表.id

若没有，你有没备份表呢？
恢复下。。。
不然就不好说了