SQLITE: Elegant way to change (swap) values of 2 rows ?

Rob_Egal · July 22, 2015, 10:09am

Hi,

I’d like to swap the values in two 2 rows like the listbox drag reorder.
I am currently using the solution to copy each field from the source and to target row (recordset). This works OK but
I have a feeling this a possible bug and looks unprofessional.

Does anyone have a better solution ?

dim rsSource as RecordSet = self.getRow( sRow ) dim rsTarget as RecordSet = self.getRow( tRow ) if rsSource<>nil and rsTarget<>nil then rsSource.Edit rsTarget.Edit dim vbuff(1) as Variant for col = kRSIdxStart+1 to rsSource.FieldCount step 2 vbuff(0) = rsSource.IdxField(col).StringValue vbuff(1) = rsSource.IdxField(col+1).IntegerValue rsSource.IdxField(col).StringValue = rsTarget.IdxField(col).StringValue rsSource.IdxField(col+1).IntegerValue = rsTarget.IdxField(col+1).IntegerValue rsTarget.IdxField(col).StringValue = vbuff(0).StringValue rsTarget.IdxField(col+1).IntegerValue = vbuff(1).IntegerValue next rsSource.Update rsTarget.Update rsSource.Close rsTarget.Close end if

Giulio_Mastrosanti · July 22, 2015, 11:36am

Hi Rob,
why swap the content of two table rows?
if it is a custom order need, couldn’t you simply have an order field and update it accordingly?
but I think I’m missing something here…

 Giulio

Rick_Araujo · July 22, 2015, 1:48pm

What do you mean? Something like this? (swapping rows):

Before:
ID   cod1    cod2    oldrow
1     a       b        1
2     c       d        2
3     e       f        3
4     g       h        4

After:
ID   cod1    cod2    oldrow
1     c       d        2
2     a       b        1
3     g       h        4
4     e       f        3

Sometimes, showing the problem is better than showing code.

Rob_Egal · July 22, 2015, 2:41pm

[quote=201891:@Rick Araujo]What do you mean? Something like this? (swapping rows):

[code]
Before:
ID cod1 cod2 oldrow
1 a b 1
2 c d 2
3 e f 3
4 g h 4

After:
ID cod1 cod2 oldrow
1 c d 2
2 a b 1
3 g h 4
4 e f 3

Sometimes, showing the problem is better than showing code.
[/code][/quote]

Hi Rick,

That’s exactly what I mean. Well, to be honest I am developing a grid control which is almost at final stage. “Just” implementing
the drag reorder stuff and I am curious how to do that. I am currently not sure if swapping the rows are the right way to go.

[quote=201867:@Giulio Mastrosanti]Hi Rob,
why swap the content of two table rows?
if it is a custom order need, couldn’t you simply have an order field and update it accordingly?
but I think I’m missing something here…

 Giulio[/quote]

Order field ? What do you mean ?

DaveS · July 22, 2015, 2:51pm

SQL has an “ORDER BY” statement

Be aware, the most database engines DO NOT GUARANTEE the physical order of retrieved results. So attempting to “physically” move their position inside the database table is most likely a futile exercise.

The best (and proper) way is to order the the information during extraction, or within what ever destination object (such as an array or listbox)

SELECT last_name,first_name
    FROM name_table
ORDER BY last_name,first_name;

Giulio_Mastrosanti · July 22, 2015, 3:08pm

I mean that, if the order of the items depends on some data inside them, I will use the order by in the way the S says

If the order of the items, for some reason, can be updated by the user, such as by drag and drop, I pout in the table an integer field named ‘order’, and when the rows are reordered, only that field is updated ( it can be updated directly putting on it the row number ).

so the next time I show the list the sql can be:

SELECT last_name,first_name
    FROM name_table
ORDER BY order;

swapping the contents of the table rows is a problematic way, both for the resason of the the not guaranteed phisical order, and for other kinds of problems ( think at related tables, linked by id, swapping row content could produce a data mess )

Rick_Araujo · July 22, 2015, 9:25pm

Giulio points the best approach; an order field. ** The code below isn’t tested, it’s off the top of my head **

UPDATE my_table SET theOrder = ID*10; – order1

SELECT *
FROM my_table
ORDER BY theOrder;

UPDATE my_table SET theOrder = ID*10 - 10 + ((ID % 2) *20); – reorder switching order of even and odd rows – order2

SELECT *
FROM my_table
ORDER BY theOrder;


Before (order1):
ID   cod1    cod2    theOrder
1     a       b        10
2     c       d        20
3     e       f        30
4     g       h        40

After (order2):
ID   cod1    cod2    theOrder
2     c       d        10
1     a       b        20
4     g       h        30
3     e       f        40

Rob_Egal · July 23, 2015, 10:18am

I guess your guys are all correct. I am retrieving the results with the “SELECT … ORDERBY %s ASC/DESC” statement (%s=column to order) and I am now struggling with a possible custom order in case they are reordered manually so the hint
with the order fields seems really good. I’ll look into it.

Rob_Egal · July 23, 2015, 3:00pm

Well after experimenting, I have found it won’t here. The displayed table on the screen is a result of the following query:

SELECT * FROM %s ORDER BY %s %s LIMIT %d OFFSET %d

ORDER BY %s %s = Column(N)", “ASC” / “DESC”

This is needed as the Columns can be Ordered ASC and DESC. The records I get from this query will b draw on the screen.
Sorting after the OrderID Column will always get the wrong result as it overwrites the actual/current ODER BY.
I kinda need the usual sort as of above and apply a custom ORDER BY where I can define the recordset to be changed.

Hmm Well, I don’t know how to explain but I hope someone can understand.

Tim_Hare · July 23, 2015, 3:54pm

I’m sorry, but that doesn’t make any sense. I can tell you, though, that using a separate column for the custom order is the correct way to do it. I can only conclude that you’re approaching the problem in an unusual way.

Rob_Egal · July 28, 2015, 6:32am

Hi Tim,

You’re right. A separate column was the way to go and I could finally implement my features such as drag reorder and insert at a specific position.
However, I am wondering if it’s possible and faster to update OrderID with only one query. I am currently
looping through the recordssets.

[code]

const sql as String = “SELECT rowid,OrderID FROM %s ORDER BY %s %s”
//…
//…
dim rs as RecordSet = self.db.SQLSelect( sq )
if rs<>nil then
dim count as integer = rs.RecordCount-1
for row as integer=0 to count
rs.Edit
rs.Field(kOrderColumn).IntegerValue = row
rs.Update
rs.MoveNext
next
end if[/code]