writing to a sql column, numbers from 1 to 1000

Question

i have a table and a field like ID(it has been added later) and i want to write to my table's ID field, numbers from 1 to 1000. for this i wrote a code like this

            for (int i = 0; i < 75440; i++)
            {
                baglan.Open();
                SqlCommand kmt = new SqlCommand();
                kmt.Connection = baglan;
                kmt.CommandText = "UPDATE EvrakArsiv SET ID='" + (i + 1) + "' ";
                try
                {
                    kmt.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                baglan.Close();
            }
            for (int i = 0; i < 75440; i++)
            {
                baglan.Open();
                SqlCommand kmt = new SqlCommand();
                kmt.Connection = baglan;
                kmt.CommandText = "UPDATE EvrakArsiv SET ID='" + (i + 1) + "' ";
                try
                {
                    kmt.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                baglan.Close();
            }

But it returns a value on the ID Field: all rows value are same. like 9, like 1745, like 3578 whats my wrong?

Well, you have no `where` clause in your query, so of course it updates the whole table with the same value. Which DBMS are you using? — fancyPants, Apr 29 '14 at 10:30
SQL 2008. how must be "Where Clause" ? can you write for this situation. i want to start from Row1 to last row — paradoxIST, Apr 29 '14 at 10:38
no the table is not blan. it have 75440 rows. i have added ID COLUMN after. because of the table has no Primary Key — paradoxIST, Apr 29 '14 at 10:46
then add a new column of type IDENTITY (seed, increment). This will insert values in the column auto incremented by the increment amount specified starting from the seed — Manish Dalal, Apr 29 '14 at 11:02
no it wont insert values incremented because the last added field ID is null. so i couldnt be a Primary Key — paradoxIST, Apr 29 '14 at 12:06

score 0 · Answer 1 · answered Apr 29 '14 at 10:34

0

WHERE clause is missing in update statements

answered Apr 29 '14 at 10:34

Manish Dalal

1,768
1
10
14

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

Try this..

Make your ID column as Identity in your DBMS. Like this http://stackoverflow.com, it will automatically insert values from 1 in table, and then use DBCC CHECKIDENT command to set Identity column for new entry..

edited May 23 '17 at 10:32

Community

1
1

answered Apr 29 '14 at 10:40

Shahid Iqbal

2,095
8
31
51

thanks body it gives THE ID field identy but numbers dont increase properly. such as from 1 to 157 is normal but then number gets value 213 then everything goes wrong. whats the reason of this. i have a big database contains 75000 rows. is this the reason of that? – paradoxIST Apr 29 '14 at 12:31
after i did your said, also must be Primary Key From Design. i did ID Primary key and numbers have been fixed. – paradoxIST Apr 29 '14 at 12:45

score 0 · Answer 3 · answered Apr 29 '14 at 11:06

0

WHERE clause is missing here. What makes your row unique give those conditions in WHERE clause

answered Apr 29 '14 at 11:06

Mohini Mhetre

912
10
29

score 0 · Answer 4 · answered Apr 29 '14 at 11:45

0

You can use the following query to update the column:

with cte
As
(
SELECT     <column list separated by comma>, Id, 
    ROW_NUMBER() over(order by <column name>) rno
    FROM EvrakArsiv 
)
Update cte
set Id = rno

It will virtually sort the data on the column mentioned in order by clause, and then set the row number to respective Id in table.

answered Apr 29 '14 at 11:45

Roopesh

279
2
7

i dont understand,can you explain with an example? – paradoxIST Apr 29 '14 at 11:50
Suppose I have a table favourite with following columns Favourites_ID, Game_name, Game_platform, DatePicked. Now I have added one more column New_ID to that. I want to set new_id with the record number in the table. Then I can use the following query `with cte As ( SELECT Favourites_ID, Game_name, Game_platform, DatePicked, new_id, ROW_NUMBER() over(order by Favourites_id) rno FROM Favourites ) Update cte set new_id = rno` – Roopesh Apr 29 '14 at 12:23
It is ok. But setting the column as primary key is not necessary. When you do select * from , by default the result comes sorted on the primary key column. So if you create the identity column without primary key and then use select * from
order by Id, it will give you correct output. Primary key is something design decisions, which should be carefully chosen.
– Roopesh Apr 29 '14 at 12:56
i know, but for me better the numbers seem properly :) – paradoxIST Apr 29 '14 at 13:06

writing to a sql column, numbers from 1 to 1000

4 Answers4