Urgent: Need help with sql statement duplicates - 2nd request for help

Transact-SQL
1

TableA:
AID Target Rpt

1 100 101
2 100 101
3 100 101
4 100 101
5 200 201
6 200 201
7 200 201
8 200 201

TableB:
BID TargetPages Status

1 100 1
2 100 2
3 8 2
5 2 1
6 2 1
7 3 2

Desired Results on TableB:
BID TargetPages Status

1 100 1
1 8 2
5 2 1
5 3 2

What I"m trying to do is normalize these tables.

  1. On tableA, the target & Rpt that are equal are considered duplicates (100 & 101), so I need to delete the dupes.
  2. Before I can make any deletes on tableA, I have to find the corresponding tableB ID's where the ID's from tableA are duplicates (ex 100 $ 101) and update the tableB ID's with just the single id of TableA (ex: select the first ID of tableA where target = 100 & Rpt = 101)
  3. The caveat of tableB, is that the TargetPages must be unique also.
  4. If we use the first set of tableA where target = 100 & Rpt = 10 and all the corresponding ID"s in TableB, AND keep targetpages unique, we would end up with the following in:

TableA:
AID Target Rpt

1 100 101

TableB:
BID TargetPages Status

1 100 1
1 8 2

I have the TableA results figured out and that works fine, but I can't get the TableB worked out and that is where I need help. This is what I have so far worked out:

SELECT *
INTO #tblTemp
FROM
(
select b.bid, b.target from tableB b
inner join tablea a on a.aid = b.bid
where (a.target = 100) and (a.rpt = 101)
) as x

select * from #tblTemp
DECLARE @BID int,@Target int
DECLARE @newIDToUSE int

DECLARE db_cursorSectionStatus CURSOR FOR
SELECT
BID, Target from #tblTemp
ORDER BY BID, Target

OPEN db_cursorSectionStatus
FETCH NEXT FROM db_cursorSectionStatus INTO @BID, @Target

WHILE @@FETCH_STATUS = 0
BEGIN

--do something here

FETCH NEXT FROM db_cursorSectionStatus INTO @BID, @Target
END

CLOSE db_cursorSectionStatus
DEALLOCATE db_cursorSectionStatus
DROP TABLE #tblTemp

create tableA:
CREATE TABLE [dbo].[TableA](
[AID] [int] NOT NULL,
[Target] [int] NOT NULL,
[Rpt] [int] NOT NULL
) ON [PRIMARY]

Create tableB:
CREATE TABLE [dbo].[TAbleB](
[BID] [int] NOT NULL,
[TargetPages ] [int] NOT NULL,
[Status] [int] NOT NULL
) ON [PRIMARY]

Insert values into tables:
INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(1, 100, 101)
GO
INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(2, 100, 101)
GO

INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(3, 100, 101)
GO
INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(4, 100, 101)
GO
INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(5, 200, 201)
GO
INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(6, 200, 201)
GO

INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(7, 200, 201)
GO
INSERT INTO [TestInsertpages].[dbo].[TableA]
([AID]
,[Target]
,[Rpt])
VALUES
(8, 200, 201)
GO

INSERT INTO [TestInsertpages].[dbo].[TAbleB]
([BID]
,[Target]
,[Status])
VALUES
(1, 100, 1)
GO
INSERT INTO [TestInsertpages].[dbo].[TAbleB]
([BID]
,[Target]
,[Status])
VALUES
(2, 100, 2)
GO
INSERT INTO [TestInsertpages].[dbo].[TAbleB]
([BID]
,[Target]
,[Status])
VALUES
(3, 8, 2)
GO

INSERT INTO [TestInsertpages].[dbo].[TAbleB]
([BID]
,[Target]
,[Status])
VALUES
(5, 2, 1)
GO
INSERT INTO [TestInsertpages].[dbo].[TAbleB]
([BID]
,[Target]
,[Status])
VALUES
(6, 2, 1)
GO
INSERT INTO [TestInsertpages].[dbo].[TAbleB]
([BID]
,[Target]
,[Status])
VALUES
(7, 3, 2)
GO

I edited this from yesterdays post, as I don't think I was clear on what the requirements are.

any help appreciated
Thanks

2

Forget looping. SQL is designed to be set-based. You can get the min AID for tableA as follows:

SELECT Target, Rpt, MIN(AID) AS AID
FROM TableA
GROUP BY Target, Rpt

We can use that as a derived table in the UPDATE and DELETE queries against TableB, or, if TableA is large, we might need to put those results into a keyed temp table and join to that. Either is easy.

But I don't understand the logic for TableB. Why does "target 8" get its AID updated from 3 to 1? I don't even see Target 8 in TableA.

3

The db is messed up. TableA is actually the result set of an outer loop (using cursor). Trying to clean this up so there is a one-to-many from TableA (parent) and TableB(child)

But I don't understand the logic for TableB. Why does "target 8" get its AID updated from 3 to 1? I don't even see Target 8 in TableA.

Target 8 is unique to TableB, has nothing to do with TableA. I know it's confusing, this is a very simplified version of what I'm trying to achieve. The real DB is much more complicated. A lot of the "duplicates" have been updated at diff times, that's why this seems so confusing.

In a nutshell:

  1. I have to find the matching id's between tableA and TableB where tableA target andRpt are equal.
  2. Update all TableB ID's to equal top 1 TableA ID where target is unique in TableB
  3. Delete all TableA ID's not equal to top 1 TableA ID (I have this figured out)
    This will give me the parent-child relationship I'm looking for and get rid of the extra data