Repository navigation
Move table data up or down in the database #2408
Description
Describe the new feature
move table data up or down in the database. maybe I didn't find it myself, but I have to move everything through the code.
It would be great to move data in the application
Additional info
Please answer these questions before submitting your feature request.
Is your feature request related to an issue? Please include the issue number.
no
Does this feature exist in another product or project? Please provide a link.
idk
Do you have a screenshot? Please add screenshots to help explain your idea.
no
Activity
chrisjlocke commentedon Sep 19, 2020
Can you explain what you mean by 'move table data up or down' - do you mean copy data from one table into another?
This is simply two lines of SQL code.
insert into [table1]select * from [table2]If you have an auto-increment column, then things are a little bit more tricky, but essentially the same...
justinclift commentedon Sep 19, 2020
Maybe@SeruiosBoss has data in a table, and is using it like a spreadsheet? eg there's nothing ordering the data (yet)
If that's the case, it'd probably be best to add a new column for ordering (eg "Line number"), so things can be reordered ~reasonably easily.
mgrojo commentedon Sep 19, 2020
@SeruiosBoss, you see why we need more complete description of features? Every one of us has understood something different. In my case, I've got that you need to move the data in some cells to previous cells, in which case you could use copy and paste, but that's probably not what you meant.
justinclift commentedon Sep 27, 2020
Closing this due to lack of response.
SeruiosBoss commentedon Sep 29, 2020
Guys,
Check this. It's what I want to
SeruiosBoss commentedon Sep 29, 2020
I Want this
chrisjlocke commentedon Sep 29, 2020
OK. Without being nasty, that's not what a database is for. Think of a database as a bucket of coins. Its just a container of data. You can choose the pull the coins out in order if you want, but you can't simply shake the bucket and look in over the top to have all the coins sorted. And even if you did or could, someone else would want the list sorted in a different way.
Simply add another column called 'sortOrder' or 'orderBy' and you can put numbers in this column, then sort on that column.
The issue comes when you want to re-sort or add another entry between two others. You need to 'renumber' your list. If you ever programmed in BASIC years ago, this is how people created programs - '10 print "hello' ... 20 goto 10'.
justinclift commentedon Sep 29, 2020
@SeruiosBoss No worries. It's just you're new to some of this stuff, and have a slight misunderstanding about how some of this works. We've all been there in one way or another. 😄
With SQL databases, you control the order of rows using an "ORDER BY" statement in your SQL query:
http://www.w3schools.com/sql/sql_orderby.asp
So, you'll need to add a new column, with row number info in it.
Kind of like this:
Does that make sense?
By the way, pick a better column name than "row_order". 😄
An alternative approach is to use a spreadsheet (eg LibreOffice Calc, or Excel). That will give you the layout you need, but isn't a database. If you definitely need a database, then you'll need to add the column like mentioned above.
Does that help?
(@chrisjlocke the above example was copied from #830, where we went through a similar thing. We might be able to use further bits from that issue for further explanation here too)
SeruiosBoss commentedon Sep 29, 2020
@justinclift , thank you, and I can just move them with row_order? But what with auto-increment? I have a Text type columns and in sqlite it can't allow me to set auto-increment.. Or must I create my own auto-increment with code?
chrisjlocke commentedon Sep 29, 2020
An auto-increment field is usually numeric, as its an incrementing number.
A table can only have one auto-increment field.
As this column is just a number, you 'move' them by changing the number within it.
So if you had
1 Cat
2 Dog
3 Rabbit
and you wanted 'rabbit' first, you'd change rabbit to number 1.
1 Cat
2 Dog
1 Rabbit
You can then 'sort' this list when you display it.
1 Cat
1 Rabbit
2 Dog
You've changed 'rabbit', but this doesn't affect the others. Each row is independent. Unique. If you want 'cat' as #2, you also have to change 'dog' to be #3. If you have 4000 rows, this is going to be problematic, unless you write some code in an external program. You could do it in SQL itself, but that's getting a bit messy too.
SeruiosBoss commentedon Sep 29, 2020
Ok thx very much.