Update rows with a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update rows with a loop

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-04-08 : 16:41:39

I want to update a column in a table (all rows) with sequential numbers.

How can I do that without a cursor? I need some type of IF Statement in the loop - like:


DECLARE @intCount int
SET @intCount = 1
WHILE @intCount < 5662
BEGIN
        Update STATION_PARAMETER_XREF SET SP_EQUIPMENT = @intCount
 
        SET @Counter = @Counter + 1
        IF Something
                BREAK
        ELSE
                CONTINUE
END

Any ideas? Thanks.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-04-08 : 17:10:29

Try this:


DECLARE @id int
SELECT @id = 0
UPDATE TOP (5661) STATION_PARAMETER_XREF
SET @id = SP_EQUIPMENT = @id + 1

Some days you're the dog, and some days you're the fire hydrant.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-04-08 : 17:16:23

Actually if you want to update all rows as opposed to a set amount, just remove the TOP (5661) from the query.

Some days you're the dog, and some days you're the fire hydrant.

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-04-08 : 18:06:53

Thanks, works like magic...lol.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-04-08 : 18:32:13

You're welcome :)

Some days you're the dog, and some days you're the fire hydrant.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-08 : 19:48:22

>> I want to update a column in a table (all rows) with sequential numbers. <<

Why, in the name of Codd? You are screwing up big time. Back to basics; a sequence is a class of attribute properties. Attributes do not just get sequenced by magic. Want an example? Your check book. Those numbers already exist and are discovered.

Your code was awful. You actually put the data type in a data element name! That was BASIC, not SQL, not ISO-11179 or valid data modeling.

Then you have a cross-ref table. That is a term from the old Network Databases and has no place in RDBMS.

We now have the ANSI/ISO feature known as SEQUENCE. It is done as “CREATE SEQUENCE Check_Number AS ..” Look it up in Denali docs.

What is a “Station_Parameter_Xref” in your mind? What is that “sp_equipment” and why it is not properly named “sp_equipment_seq”? And what would it mean, since equipment usually does not have a sequence?

You are asking the wrong questions because you have no idea how to do a data model or how RDBMS works. This might be too much to learn in a forum, but we can try

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-09 : 07:22:30

Celko:

you are constantly writing about ISO/ANSI Standards, rules, etc., which is great, but most of the time you are nagging about how people write their code.
just once, try to enlighten everybody here with a chunk of code or bring up the solution that starts with SELECT, UPDATE, DELETE or INSERT. and not with ISO, BASIC, ANSI, ....

no hard feelings :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources