July 21, 2008 at 3:50 am
Hi,
I'm looking for a solution to a UPDATE query I'm trying to perform.
Basically, I have an existing table (Cleansing.Customer) that contains a column 'AccountNum' (this forms one part of the Primary Key) and 'Group_Acc'.
NOTE: 'AccountNum' may appear more in more than one row.
I need to read through the table and then assign a unique number in column 'Group_Acc' for each 'AccountNum'.
So,
AccountNum
123
123
345
456
456
456
678
789
becomes..
Group_Acc AccountNum
1 123
1 123
2 345
3 456
3 456
3 456
4 678
5 789
Any ideas please?
Thanks in advance,
Neal
July 21, 2008 at 4:04 am
; WITH Customers
AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY AccountNum ORDER BY AccountNum ) AS RowNumber, *
FROM Cleansing.Customer
)
UPDATE Customers
SET Group_Acc = RowNumber
--Ramesh
July 21, 2008 at 4:25 am
U can do tht by using Cursors, as like:
DECLARE @OldColumn INT
DECLARE @NewCount INT
SET @NewCount = 1
DECLARE MyCursor CURSOR
FOR
SELECT AccountNum FROM Cleansing.Customer GROUP BY AccountNum
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @OldColumn
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.TSort
SET Group_Acc = @NewCount
WHERE AccountNum = @OldColumn
SET @NewCount = @NewCount + 1
FETCH NEXT FROM MyCursor INTO @OldColumn
END
CLOSE MyCursor
DEALLOCATE MyCursor
July 21, 2008 at 4:27 am
Thanks Ramesh
I'm getting the following error message though..
Msg 207, Level 16, State 1, Line 8
Invalid column name 'RowNumber'.
I'll check out the other post from thegreatsamarth...
July 21, 2008 at 4:34 am
Thanks guys
Just tried out the reply from thegreatsamarth, worked a treat (just altered the UPDATE dbo.TSort line).
Fantastic!!
July 21, 2008 at 4:36 am
thegreatsamarth (7/21/2008)
U can do tht by using Cursors, as like:
Cursors = row based processing = slow.
SELECT ROW_NUMBER() OVER( PARTITION BY AccountNum ORDER BY AccountNum ) AS RowNumber,
Ramesh, isn't yours going to produce the following?
Group_Acc AccountNum
1 123
2 123
1 345
1 456
2 456
3 456
1 678
1 789
Try this?
UPDATE c
SET GroupAcc = Ranking
FROM Cleansing.Customer c inner join (
SELECT AccountNum, DENSE_RANK() OVER (ORDER BY AccountNum) AS Ranking FROM Cleansing.Customer) Ranked
ON c.AccountNum = Ranked.AccountNum
Not tested, because I don't have your table structure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2008 at 4:50 am
Though the solution provided thegreatsamarth is correct, but i doesn't recommend it usng cursors, as this is row-by-row process and would probably be slow in execution...
And my solution is absolutely incorrect, probably because of having too much coffee in daytime...
Here is an correct version...
IF ( OBJECT_ID( 'tempdb..#Customers' ) IS NOT NULL )
DROP TABLE #Customers
CREATE TABLE #Customers
(
CustomerID INT NOT NULL IDENTITY,
AccountNum VARCHAR(10) NOT NULL,
Group_Acc VARCHAR(10) NULL
)
INSERT#Customers( AccountNum, Group_Acc )
SELECTTOP 100 DENSE_RANK() OVER( PARTITION BY object_id ORDER BY name), NULL
FROMsys.columns
ORDER BY NEWID()
; WITH CustomersCTE
AS
(
SELECT DENSE_RANK() OVER( ORDER BY AccountNum ) AS RowNumber, *
FROM #Customers
)
UPDATE CustomersCTE
SET Group_Acc = RowNumber
SELECT * FROM #Customers ORDER BY AccountNum
Edit:
Itchy fingers:D
--Ramesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply