x

Loop through a table to update Records using SQL 2000

Folks

I have this table called TblCombine
Sample data

Ldg,  Bnk,      Combine,   Dupe  
BQ    55.23     BQ55.23    0  
BQ    65.23     BQ65.23    0  
BQ    75.23     BQ75.23    0  
BQ    85.23     BQ85.23    0  
BQ    85.23     BQ85.23    0  
BQ    85.23     BQ85.23    0  
Int   95.23     Int95.23   0  
Int   105.23    Int105.23  0   
Int   95.23     Int95.23   0  
Int   95.23     Int95.23   0  

I join the Ldg and Bnk fields into the Combine field.
My problem is I need a process to Order the Combine field
Then loop through the combine field and if it's not equal to the combine field above it the Dupe field is Updated to 1, if it is equal the field above it is updated to 2 and so on,

Expected Result

Ldg,  Bnk,      Combine,   Dupe  
BQ    55.23     BQ55.23    1  
BQ    65.23     BQ65.23    1  
BQ    75.23     BQ75.23    1  
BQ    85.23     BQ85.23    1  
BQ    85.23     BQ85.23    2  
BQ    85.23     BQ85.23    3  
Int   95.23     Int95.23   1  
Int   95.23     Int95.23   2  
Int   95.23     Int95.23   3  
Int   105.23    Int105.23  1

I have done this in VB where in access as I can follow the process but I am lost at the moment in SQL.
Regards & Thanks
Pator

more ▼

asked Nov 03 '09 at 08:23 AM in Default

pator gravatar image

pator
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

8 answers: sort newest

The answer would probably be to stage the import into a temporary table with an identity column, calculate/update the dupe count, and then migrate to your final table.

If this intermediate processing is acceptable then Melvyn or Matt would probably have the queries you need ready to present to you based on their comments.

more ▼

answered Nov 04 '09 at 03:20 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left

Oh, sorry... I'm not sure how I lost track of this one so very badly.

Pator, here's the SQL Server 2000 method I was thinking off. The clustered index, TABLOCKX, and MAXDOP thingies are absolutely essential...

--===== Create and populate a test table.  -- This is NOT a part of the solution  CREATE TABLE #TblCombine  (  Ldg VARCHAR(5),  Bnk DECIMAL(9,2),  Combine VARCHAR(20),  Dupe INT  )

INSERT INTO #TblCombine (Ldg,Bnk,Combine,Dupe) SELECT 'BQ','55.23','BQ55.23','0' UNION ALL SELECT 'BQ','65.23','BQ65.23','0' UNION ALL SELECT 'BQ','75.23','BQ75.23','0' UNION ALL SELECT 'BQ','85.23','BQ85.23','0' UNION ALL SELECT 'BQ','85.23','BQ85.23','0' UNION ALL SELECT 'BQ','85.23','BQ85.23','0' UNION ALL SELECT 'Int','95.23','Int95.23','0' UNION ALL SELECT 'Int','105.23','Int105.23','0' UNION ALL SELECT 'Int','95.23','Int95.23','0' UNION ALL SELECT 'Int','95.23','Int95.23','0'

--=================================================================== -- Solution starts here --=================================================================== --===== If you don't have THIS index on the table, then it won't work CREATE CLUSTERED INDEX IX_TblCombine_CoverUpdate ON #TblCombine (Combine)

--===== Create a couple of necessary obviously named variables DECLARE @PrevDupe INT, @PrevCombine VARCHAR(20)

--===== Update the dupe column UPDATE #TblCombine SET @PrevDupe = Dupe = CASE WHEN Combine = @PrevCombine THEN @PrevDupe+1 ELSE 1 END, @PrevCombine = Combine FROM #TblCombine WITH(TABLOCKX) --Absolutely essential. DO NOT REMOVE OPTION (MAXDOP 1) --Absolutely essential. DO NOT REMOVE

--===== Show the result SELECT * FROM #TblCombine ORDER BY Combine,Dupe
more ▼

answered May 09 '10 at 12:30 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 8

(comments are locked)
10|1200 characters needed characters left

Jeff I would be interested in a SQl solution, I did get around the problem by exporting the sql query as a text file and then re-import into the database. I know it's not a perfect solution but it got the job done. However a SQl solution would still be very benificial. Regards & Thanks Pator

more ▼

answered Mar 08 '10 at 12:25 PM

pator gravatar image

pator
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

Pator,

I realize this is a pretty old post... do you still need an answer on this because, if you do, I have one. I just don't want to take the time to write it up if you don't need it.

more ▼

answered Mar 07 '10 at 01:04 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 8

(comments are locked)
10|1200 characters needed characters left

Matt ,
Thanks for the mail. this is my table,

1 LDG varchar 50 1
0 BNK decimal 9 1
0 COMBINE varchar 50 1
0 DUPE numeric 9 1
0 UniqueID varchar 50 1
0 id int 4 1

I have got a little further with my problem.
The ID field in table is not used and can be changed to any field type if required.

I use the following SQL to create the data the way I require it. Then I run the the query
to file and use DTS to import it into a temp table and use sql to transfer the required data into TBlCombine. I know it's not a perfect answer but it's gets the job done. But it would be better just to run an update process of some sort. I don't have the skill as yet to modify this SQl to change it to an update process.

DECLARE @Test table (Ldg varCHAR(50),

Bnk decimal(9,2),

Combine varchar(50),

Dupe int IDENTITY(1,1))

INSERT INTO @Test (Ldg, Bnk, Combine)

SELECT Ldg, Bnk, Ldg+CAST(Bnk as varchar(25))

FROM TblCombine ORDER BY TblCombine.LDG , TblCombine.BNK , TblCombine.COMBINE

SELECT rtrim(TblCombine.LDG) as Ldg, TblCombine.BNK, rtrim(TblCombine.LDG) + rtrim(cast(TblCombine.BNK as varchar(50))) as Combine , test.Dupe - TblCombine.Dupe + 1 AS Dupe

FROM @Test test

INNER JOIN (SELECT Ldg, Bnk, MIN(Dupe) AS Dupe

        FROM @Test
        GROUP BY Ldg, Bnk)Tblcombine

on Test.Ldg = TblCombine.Ldg where test.Bnk = TblCombine.Bnk

Data after SQl runs,

ldg bnk combine Dupe

BQ 75 BQ75.00 1
BQ 85 BQ85.00 1
BQ 85 BQ85.00 2
BQ 85 BQ85.00 3
BQ 95 BQ95.00 1
INT 95 INT95.00 1
INT 95 INT95.00 2
INT 95 INT95.00 3
INT 105 INT105.00 1
INT 105 INT105.00 2
INT 106 INT106.00 1
INT 107 INT107.00 1

AS you can see the number in the Dupe field is based on the combine field, which increases by 1 each time the field is the same.

Any further help would be great.

Regards

Pator

more ▼

answered Dec 01 '09 at 01:50 PM

pator gravatar image

pator
13 1 1 1

@Pator - sorry - I only just saw this now, as it didn't alert for me. I will let Jeff answer though, his solution will be about 40x better than anything I would write.
Mar 08 '10 at 12:54 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x472

asked: Nov 03 '09 at 08:23 AM

Seen: 2475 times

Last Updated: Nov 05 '09 at 05:25 AM