I'm re-posting this, and hope I've made myself clearer as I didn't have any luck on my original post getting any help. I am stuck and can't seem to move past this.
ableA:
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.
- On tableA, the target & Rpt that are equal are considered duplicates (100 & 101), so I need to delete the dupes.
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