Updating a table starting with the first empty column in a specific row

Question

Post reply

Updating a table starting with the first empty column in a specific row

ziedkallel

SSC Veteran

Points: 260
More actions
August 4, 2010 at 11:11 pm

#225796

Hi,
I'm struggling to find a solution to this (very likely simple) problem:
I want to populate some rows in a table where the column RequestID is empty. I want the update query to start from the first row were RequestID is empty and continue downwards (RequestID will be incremented by '1'). Tried to use cursor but wasn't successful (the column RequestID gets populated with '1' all the way down!). Any help would be much appreciated:
Declare @i integer
set @i=0
declare CustList cursor for
SELECT * from FALSEDESCRIPTION WHERE RequestID =''
OPEN CustList
FETCH NEXT FROM CustList
While (@@FETCH_STATUS = 0)
Begin
set @i=@i+1
UPDATE FALSEDESCRIPTION SET RequestID=@i
FETCH NEXT FROM CustList
End
Close CustList
Deallocate CustList

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

See if this helps

; WITH cte_FALSEDESCRIPTION AS

(

SELECTROW_NUMBER() OVER( ORDER BY RequestID ) RowNum, *

FROMFALSEDESCRIPTION

WHERERequestID = ''

)

UPDATEcte_FALSEDESCRIPTION

SETRequestID = RowNum

Not tested as you did not provide the sample data to test with.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ziedkallel SSC Veteran Points: 260 More actions · Answer 2

CELKO (8/5/2010)
This makes no sense. A table has no ordering by definition. There is no concept of "first", "next", "up" or "down" in RDBMS. That is what you have with (uggh!) magnetic tape files and sequential processing. You have missed the whole idea of set oriented, declarative programming.
What are you trying to do? Where is your DDL?

I'm assuming this is a response to the previous posting...

Sorry foks; just reallizing that this forum deals with SQL 2008 only. I'm still using the pre-historic SQL 2000!

I would appreciate it if someone directs me to the correct sub-forum to ask my question and elaborate further. Best, Z.

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 3

ziedkallel (8/5/2010)
CELKO (8/5/2010)
This makes no sense. A table has no ordering by definition. There is no concept of "first", "next", "up" or "down" in RDBMS. That is what you have with (uggh!) magnetic tape files and sequential processing. You have missed the whole idea of set oriented, declarative programming.
What are you trying to do? Where is your DDL?
I'm assuming this is a response to the previous posting...
Sorry foks; just reallizing that this forum deals with SQL 2008 only. I'm still using the pre-historic SQL 2000!
I would appreciate it if someone directs me to the correct sub-forum to ask my question and elaborate further. Best, Z.

Try here for the equivalent SQL 2000 forum.

I think Joe Celko's response was directed at your first post. He's quite right to point out that tables don't have any "order" as such and there is no "up" or "down", so the idea of starting from the first row and working down doesn't make any sense. However, it may be possible to help you further if you can provide some more info on table structure (ddl) and the nature of this requestID column. What are it's non-empty values for instance?

Duncan

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Bearing in mind what others have said, can you write a query which returns your empty columns in the order in which you would like them sequentially populated? You will also find a PK column handy in the output. Something like:-

SELECT FalseDescriptionID, HandyOrderingColumn

FROM FALSEDESCRIPTION

WHERE RequestID =''

ORDER BY HandyOrderingColumn

Put in actual table/column names.

Please also provide some sample data in the format recommended in the link in my sig.

What is the nature of the table, is it in production with thousands of hits per hour, out of scope to users, or something in between?

There are at least two ways to do what you are attepting - a Running Totals update, or an UPDATE FROM using a temp table derived from the above query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jamie Ingram-729524 SSCommitted Points: 1573 More actions · Answer 5

Create Table #Temp

(

NewID int identity(1,1),

);

Insert into #Temp

Select * from <other table>

delete from <other table>;

insert into <other table>

select coalesce(RecordID, newID) As ID, <other fields>

from #Temp;

drop table #Temp;

We are the pilgrims, master.
We shall go always, a little further.