Update linked tables with multiple rows

I have an existing database with a table structure like this:

TABLE [Parent]
  [Parent_ID] UNIQUEIDENTIFIER 

TABLE [ChildA]
  [ChildA_ID] INT
  [Parent_ID] UNIQUEIDENTIFIER FK -> [Parent].[Parent_ID]

TABLE [ChildB]
  [ChildB_ID] INT
  [Parent_ID] UNIQUEIDENTIFIER FK -> [Parent].[Parent_ID]

To support future functionality, I need link rows of ChildA and ChildB so that there is exactly one ChildB for each ChildA . The new structure should be

TABLE [ChildA]
  [ChildA_ID] INT
  [Parent_ID] UNIQUEIDENTIFIER FK -> [Parent].[Parent_ID]

TABLE [ChildB]
  [ChildB_ID] INT
  [ChildA_ID] INT FK -> [ChildA].[ChildA_ID]
  [Parent_ID] UNIQUEIDENTIFIER FK -> [Parent].[Parent_ID]

Now, both tables contain several rows in both the ChildA and ChildB tables that are nearly duplicates. For the sake of this question lets assume that the number of rows in each table is the same for any given Parent_Id . I need to pick a unique ChildA for each ChildB , even if I have to pick them at random.

This works for cases where there is exactly one row in each table:

UPDATE b
SET    [ChildA_ID] = a.[ChildA_ID]
FROM   [ChildB] b
       INNER JOIN [ChildA] a ON a.[Parent_ID] = b.[Parent_ID]

I don't have any other criteria to add to the JOIN . I think ROW_NUMBER would work, but this database is running on SQL Server 2000. How would I go about assigning a ChildA_ID to each ChildB_ID when there are multiple records?

链接地址: http://www.djcxy.com/p/95672.html

上一篇: 将SQL Server身份主键从1改为顺序

下一篇: 用多行更新链接表