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, 2009 at 08:23 AM in Default

avatar image

pator
13 1 1 3

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

8 answers: sort voted first

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, 2009 at 03:20 PM

avatar image

Blackhawk-17
12k 30 35 42

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

Can you post the table definitions? I have a solution which would be reasonably quick, but it needs either a suitable clustered index, or there to be another column in the table which can uniquely identify each entry.

If you can edit your question to include that, then I will post you a solution.

more ▼

answered Nov 03, 2009 at 08:42 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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

A couple of issues you'll need to address:

  1. You're not specifying a unique ordering. Why should the rows be ordered the way they are (within each sub-class of being the same as far as the combine field goes)?
  2. Do you need to store the duplicate information that's present in the Combine field? If you need this for the application, I would suggest generally calculating that in a stored procedure, or a view.

Similarly, you will be able to get the dupe out by running a select along the lines of:

SELECT Ldg, Bnk, Combine, Dupe, ROW_NUMBER() OVER (PARTITION BY Combine ORDER BY Bnk)
FROM MyTable
ORDER BY Combine

But this is still going to struggle to be useful without a unique ordering.

more ▼

answered Nov 03, 2009 at 08:44 AM

avatar image

Melvyn Harbour 1 ♦♦
1.4k 19 40 26

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

Folks, I appreciate your help ,Unfortunately I have no control over the data I import, It is from an external source and what I see is what I get.

I'm new to SQL so I'm still in the dark a lot about what I can and cannot do. I just taught that if it could be done in VB there was a chance it could be done in SQL. But I will keep trying. Thank you All

more ▼

answered Nov 03, 2009 at 01:09 PM

avatar image

pator
13 1 1 3

(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, 2009 at 01:50 PM

avatar image

pator
13 1 1 3

@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, 2010 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.

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:

x1066
x501

asked: Nov 03, 2009 at 08:23 AM

Seen: 3268 times

Last Updated: Nov 05, 2009 at 05:25 AM

Copyright 2016 Redgate Software. Privacy Policy