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

pator gravatar image

pator
13 1 1 1

(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

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Lack of a unique row identifier was the same problem I ran into with answering it!
Nov 03, 2009 at 08:44 AM Melvyn Harbour 1 ♦♦

Table TblCombine Design view

LDG varchar 50 1 BNK decimal 9 1 COMBINE varchar 50 1 DUPE numeric 9 1 UniqueID varchar 50 1

The unique field is to be a combination of the Combine Field and the Number in the dupe filed after the the dupe field is populated with the process I require. Thanks for looking at the Problem. Regards Pator
Nov 03, 2009 at 09:11 AM pator
I added a comment on Mel's post - he's right in that there needs to be a unique identifier on each row prior to the operation. Is it possible to add an IDENTITY field to the table?
Nov 03, 2009 at 11:50 AM Matt Whitfield ♦♦
(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

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

Melvyn Thanks for the reply yes I need to keep the Duplicate data in the combine field. I have a field called UniqueID which is populated when I join the combine field with the number in the dupe field and that makes it a unique field for me. Hopes this makes sense to you. Regards Pator
Nov 03, 2009 at 09:29 AM pator
But you're going to need some form of row identifier before you can do this. The Combine + Dupe option you say will only be unique AFTER the work has been done. You need something that's unique BEFORE that.
Nov 03, 2009 at 09:31 AM Melvyn Harbour 1 ♦♦
Melvyn is absolutely right - you need a way in which you can uniquely identify the rows in order to be able to apply the 'Dupe' value efficiently. I must say though, that something is a bit fishy about this - there is something wrong with the design of the database, because this sort of dupe assignment should never be necessary in a properly 3NF database schema.
Nov 03, 2009 at 09:45 AM Matt Whitfield ♦♦
An identity column would have been a great addition for sure. By populating the dupe field they are attempting to get the table back to 3NF. I think this solution will need a cursor that updates the dupe column with a count variable per combine.
Nov 04, 2009 at 11:20 AM Blackhawk-17
(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

pator gravatar image

pator
13 1 1 1

You've got essentially the same problems in VB though - how are you going to decide which of the three identical rows gets marked as 1, 2 and 3 respectively? Yes, they're identical in the sense of the data contain, but they are logically separate items.
Nov 03, 2009 at 01:35 PM Melvyn Harbour 1 ♦♦
I don't think it matters to them which one is marked as which duplicate. The only thing is that they are identified and assigned a count value. I would guess they are doing a modified bubble sort in VB and looping through the duplicates to renumber them.
Nov 04, 2009 at 11:17 AM Blackhawk-17
That's true Blackhawk, It's not relevant whcih record has the 1 , 2 or 3 for my purposes. Regards, Pator
Nov 04, 2009 at 11:19 AM pator
Blackhawk , Thanks for the reply ,yes I would try that temp table process, If I can get the code to start the process just to give me an idea how to go about it. Regards and Thanks. Pator
Nov 05, 2009 at 07:18 AM pator
(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

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

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:

x986
x473

asked: Nov 03, 2009 at 08:23 AM

Seen: 2728 times

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