Thanks for the question, Sammy.
Asked: March 19, 2017 - 1:54 pm UTC
Last updated: March 23, 2017 - 3:15 am UTC
Version: Oralce Sql Developer Version 4.2.0.16.260
Viewed 1000+ times
You Asked
Hi,
I am trying to update as column id in table with 8350 record.
There is ID column with scatter id number
CREATE TABLE DB_Trac
(
T_ID NUMBER(9,0) Not Null,
FV_Place NUMBER
)
Insert into DB_Trac (T_ID,FV_Place ) values ('2','15.93');
Insert into DB_Trac (T_ID,FV_Place ) values ('11','5.43');
Insert into DB_Trac (T_ID,FV_Place ) values ('31','1.37');
Insert into DB_Trac (T_ID,FV_Place ) values ('41','-15.93');
Insert into DB_Trac (T_ID,FV_Place ) values ('151','285.23');
--- Not working as per requirement ----
UPDATE DB_Trac SET T_ID = T_ID + 1 ----
but the row number are not in sequence there are gaps in numbering
===================================================
Output: Required
T_ID FV
1 (value)
2 (value)
3 (value)
4 (value)
5 (value)
.
.
3850 (value)
Please help I need to update T_ID starting with 1 and next record by 2,3,4, ....,8350
and Connor said...
SQL> drop table db_trac purge;
drop table db_trac purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> CREATE TABLE DB_Trac
2 (
3 T_ID NUMBER(9,0) Not Null,
4 FV_Place NUMBER
5 );
Table created.
SQL>
SQL>
SQL> Insert into DB_Trac (T_ID,FV_Place ) values ('2','15.93');
1 row created.
SQL> Insert into DB_Trac (T_ID,FV_Place ) values ('11','5.43');
1 row created.
SQL> Insert into DB_Trac (T_ID,FV_Place ) values ('31','1.37');
1 row created.
SQL> Insert into DB_Trac (T_ID,FV_Place ) values ('41','-15.93');
1 row created.
SQL> Insert into DB_Trac (T_ID,FV_Place ) values ('151','285.23');
1 row created.
SQL>
SQL> select * from db_trac;
T_ID FV_PLACE
---------- ----------
2 15.93
11 5.43
31 1.37
41 -15.93
151 285.23
5 rows selected.
SQL>
SQL> update db_trac set t_id = rownum;
5 rows updated.
SQL>
SQL> select * from db_trac;
T_ID FV_PLACE
---------- ----------
1 15.93
2 5.43
3 1.37
4 -15.93
5 285.23
5 rows selected.
SQL>
SQL>
SQL>
But note - typically an "ID" column might just be a means to uniquely identify a row - so it doesn't really matter what value it has, or whether there are gaps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment