Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Updating using current row number

Posted on 2004-08-10
4
Medium Priority
?
1,963 Views
Last Modified: 2008-03-17
I'm trying to update a field for a series of records to contain sequential numbers, like a fix to remove gaps in the numbers.

So that, if I can return the following table:

ID  |  Value
87  |    5
89  |    11
95  |    18
98  |    23

I want to be able to do a single UPDATE query, to make:

ID  |  Value
87  |    1
89  |    2
95  |    3
98  |    4

I would prefer an answer that is the same in both MSSQL and MySQL, but an answer for either one of them will earn the points
0
Comment
Question by:neur0maniak
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 11761439
In MSSQL Server, I would perform this operation like this:

CREATE TABLE TEMP ( ID INT )
INSERT INTO TABLE TEMP SELECT ID FROM yourtable
CREATE CLUSTERED INDEX CIDX_ID ON TABLE TEMP ( ID )
ALTER TABLE TEMP ADD VALUE INT IDENTITY (1,1)
UPDATE yourtable SET VALUE = TEMP.VALUE
FROM yourtable JOIN TEMP on TEMP.ID = yourtable.ID
DROP TABLE TEMP

I don't know about how I would do this in MySQL, but I assume it won't work that way...

Actually, if you use a cursor, this can be done quite easily (pseudo-code):
open the cursor (SELECT ID, VALUE FROM yourtable ORDER BY ID ASC )
@value = 0
for each row
   @Value = @Value+1
   update current row SET VALUE = @Value


That's it. Of course, not a single UPDATE. You might think about an update like the current:
UPDATE yourtable SET Value = (select count(*) from Yourtable ti WHERE ti.ID <= yourtable.ID )

It can work in MSSQL (not sure in MySQL), but performance is really bad...

Cheers
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question