Android Question update sql Id

fanfalveto · Aug 27, 2014

any idea how to update Id column ,for example i have Id: 1,2,3,6,9 and i want to order again Id: 1,2,3,4,5.
with this put all Id with the last number (logical)

B4X:

Sub ordenaid
    Dim Cursor As Cursor
    Dim lon As Int
    Cursor=sq.ExecQuery("Select Id from evolucion")
    lon=Cursor.RowCount
    Dim i As Int
    For i=1 To lon
    Cursor.Position=i
If i=1 Then
    sq.ExecNonQuery("UPDATE evolucion SET Id = ('"&i&"')")
    Next
End Sub

do not control much of sql.
thank you

Mahares · Aug 27, 2014

You need to:
1. Create a new table from original table with same fields as the original
2. Copy each record from original table to new table
3. Delete original table
4. Rename new table to original table name. See below code. If you do not find a better way, I can post full code.

B4X:

 'Create a new table from original table with same fields as the original
    txt="CREATE TABLE tblNew (ID INTEGER PRIMARY KEY ,COUNTRY TEXT, POPULATION TEXT)"
    SQL1.ExecNonQuery(txt)

    'Copy each record from original table to new table
    SQL1.BeginTransaction
    Cursor1=SQL1.ExecQuery("SELECT * FROM tblOriginal")
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        txt="INSERT INTO tblNew  VALUES(?,?,?)"
       SQL1.ExecNonQuery2(txt, Array As Object(i+1,Cursor1.GetString("COUNTRY"),Cursor1.GetString("POPULATION")))
    Next
    SQL1.TransactionSuccessful
    SQL1.EndTransaction
   
    'Delete original table
    txt="DROP TABLE tblOriginal"
    SQL1.ExecNonQuery(txt)
   
    'Rename new table to original table name
    txt="ALTER TABLE tblNew RENAME TO tblOriginal"
    SQL1.ExecNonQuery(txt)

sorex · Aug 28, 2014

another method...

read in all records, the record set is already in the good order for what you want
truncate the table
loop through the recordset and insert it again but instead of using the id you use the variable use for the "for ? loop" and add 1 to it.

keirS · Aug 28, 2014

Last two answer seem a bit complex to me.
Given a table evolucion with the fields ID, and DESCRIPTION:

B4X:

CREATE TABLE evolucion2 as SELECT * FROM evolucion ORDER BY ID
DELETE FROM  evolucion
INSERT INTO  evolucion  (ID,DESCRIPTION) SELECT ROWID AS ID, DESCRIPTION  FROM evolucion2 ORDER BY ROWID
DROP TABLE evolucion2

eps · Aug 28, 2014

A good suggestion by keirS

But it has to be begged why you are doing this..!?! The id, should be a persistent value, you can give them another column, which indicates the sort order or display order and fiddle around with this instead.

The main issue will be related records - although I am guessing that you only have 1 table and no related child records to consider in this example.

Another issue will be when the number of records becomes sizeable. You are going to have to read and write a lot of records to achieve what you want to do.

I think you need to carefully consider your design in this area and attempt to work out exactly what you are trying to achieve and some of the implications.

fanfalveto · Aug 28, 2014

Thank you to all,i must need do that (is not the better i know) because i have a problem and this is the better solution (i was think) now i find other method to solve the problem.
Thank you

Android Question update sql Id

fanfalveto

Active Member

Mahares

Expert

sorex

Expert

keirS

Well-Known Member

eps

Expert

fanfalveto

Active Member

Similar Threads

Android Question update sql Id

fanfalveto

Active Member

Mahares

Expert

sorex

Expert

keirS

Well-Known Member

eps

Expert

fanfalveto

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency