1

I have searched, and perhaps I am not asking the question correctly.

I have inherited a nasty database and am trying to "normalize" it. I have broken one table into two: Owners and Buildings And now I have two One to One tables.

I know how to deleted duplicate records (in the Owners table) but I do not know how to then update the "one to many" related table.

I have one table "Owners" and one table "Owners(one) to Buildings(many)"

"Owners" Table schema:

CREATE TABLE
    [dbo].[tbl_BuildingOwners]
(
    [OwnerID] [int] IDENTITY(1,1) NOT NULL,
    [OwnerName] [nvarchar](255) NULL,
    [OwnerAddress1] [nvarchar](255) NULL,
    [OwnerAddress2] [nvarchar](255) NULL,
    [OwnerAddress3] [nvarchar](255) NULL,
    [OwnerCity] [nvarchar](255) NULL,
    [OwnerState] [nvarchar](255) NULL,
    [OwnerZip] [float] NULL,
    [OwnerZipExt] [float] NULL,
    [OwnerPhone] [nvarchar](255) NULL,
    [OwnerFax] [nvarchar](255) NULL
)

"Owners(one) to Buildings(many)" Relational Table schema:

CREATE TABLE
    [dbo].[BuildingOwnerID]
(
    [OwnerRelationshipID] [int] IDENTITY(1,1) NOT NULL,
    [OwnerID] [int] NOT NULL,
    [FileNumber] [nvarchar](255) NOT NULL
)

I need to delete the duplicates in the BuildingOwners table and update the OwnerID in the BuildingOwnerID table to the DISTINCT OwnerID that is left in the BuildingOwners table.

I hope this made sense.

I have already tried this but could not make it work for me. Lastly, I can use either SQL sever or MS Access which ever is easier.

Community
  • 1
  • 1
Diana
  • 244
  • 2
  • 12

1 Answers1

0

To remove duplicate you can use below query (sample query to remove duplicate state entries [duplicate by Country and State])....

WITH    dupDel
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY country, STATE ORDER BY country ) AS RowNum
               FROM     tblTest
             )
    DELETE  FROM dupDel
    WHERE   RowNum > 1
Vishal Patel
  • 953
  • 5
  • 11