SQLTeam.com | Weblogs | Forums

Need help with


#1

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.

  1. 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


#2

Hi Ann1,

Please try and make changes as per your requirement

SELECT

  • INTO #tableC
    FROM (SELECT
    ROW_NUMBER() OVER (PARTITION BY [Target], [Rpt] ORDER BY AId) Rno,

FROM tableA) AS dt
WHERE Rno = 1

SELECT
*
FROM tableB
UPDATE B
SET Bid = C.AID
FROM tableB B
INNER JOIN #tableC C
ON B.[TargetPages ] = C.Target

UPDATE B
SET Bid = C.AID
FROM #tableC C
INNER JOIN tableA A
ON C.Target = A.Target
AND C.Rpt = A.Rpt
INNER JOIN tableB B
ON B.BID = A.AID

SELECT
*
FROM tableB

SELECT
*
FROM (SELECT
ROW_NUMBER() OVER (PARTITION BY [TargetPages ], bid ORDER BY bid) Rno,
*
FROM tableB) AS dt
WHERE Rno = 1


#3

It would help if your subject was a little more descriptive. I usually read those whom I think I can either help or learn from their problem.


#4

Thank you so much for replying and trying to help. I was beginning to despair. I think this is enough to get me going.

Thank you again - it was very kind and very much appreciated


#5

You're right. I'm very sorry - I was in such a muddle, that I didn't even notice.

My apologies


#6

Been there myself many times!


#7

Thank you for understanding. The most frustrating part for me is, I'm a programmer, not a dba, and while I can handle mundane everyday stuff on a db, I don't live in that world, so more complex problems I struggle through. We do have a dba, but he comes to ME for help! LOL!


#8

Started programming in 1995, but have 7 years of client/server programming and this past year of T-SQL report writing, so I still think of myself as a programmer, but often end up helping DBAs in the direction to go due to experience. I know what you mean!