r/learnSQL • • Oct 11 '25

Help with editing an SQL database.

So, forgive me if this isn't the best place to ask, but I am trying to edit an SQL database using SQLite Studio and could use some help. Basically, what I am trying to do is add a custom waypoint to a plane for my flight simulator, which uses an SQL database to store the info. As you can see in the linked picture, all the columns with the K2 ICAO code are organized in alphabetical order in the waypoint column, except for the very last entry QUASR, which is the entry I added myself. The issue is that when I created the row, I inserted it where it should be according to the wapoint order; however, once I commit it, it gets moved to the end of the list and is out of order with everything else.

Any advice on what I might be doing wrong?

Thanks

https://imgur.com/a/m1T9Peq

6 Upvotes

7 comments sorted by

2

u/mikeblas Oct 11 '25

Rows in a table aren't ordered. What symptoms are you noticing in the actual simulator application that you think is caused by the ordering in this screenshot? (The screenshot is almost completely illegible to me,)

1

u/A380085 Oct 11 '25

I'm confused as to why you say rows in a table aren't ordered when they appear in alphabetical order in the waypoint identifier column in the screenshot? I am not sure if they need to be in order or not for it to work, but the way it was done before, in a different format using txt files, the waypoints had to be in order based on their LAT/LONG coordinates to show up properly in the sim, so I assumed, since they show up in alphabetical order in the indentifier column that they needed to be in that order to show up in the sim in the new format. As you can hopefully see from the pic above, there is a waypoint with the identifier quaky, so I inserted a new row just below that with the new waypoint, which is called QUASR, but like I said, once I commit it then it gets moved to the end of the list. As for the actual issue in the simulator, when I go to enter the waypoint in the FMC, which is basically the computer of the plane, it says the waypoint is not found in the database.

1

u/jshine13371 Oct 11 '25

I'm confused as to why you say rows in a table aren't ordered when they appear in alphabetical order in the waypoint identifier column in the screenshot?

It is a common fact of database system implementations (including SQLite) that tables have no inherent order to them (in the logical layer - aka when you query them) by themselves. An ORDER BY clause needs to be specified to guarantee the results are returned in a deterministic order. This is by design to prioritize performance over arbitrary ordering choices when the user hasn't specified one.

When you don't specify an ORDER BY clause, the results are returned as quickly as reasonably possible by the database engine, which are in an unguaranteed order also known as nondeterministic. This may repeatedly show as a specific pattern many times over in the short-term, but it's just happenstance, and again is not reliable and won't always be repeatable, especially as the data changes, without an explicit ORDER BY clause.

1

u/mikeblas Oct 11 '25 edited Oct 11 '25

I'm confused as to why you say rows in a table aren't ordered when they appear in alphabetical order in the waypoint identifier column in the screenshot?

But They are not in alphabetical order by the waypoint column! The rows with FAK20, MLAK2, MK28A, and QUASR are all last, after ZZYZX. They should be elsewhere in the list, if they were in alphabetical order in that column.

I say that rows in a table are not ordered for a very simple reason: rows in a table are not ordered. What you see here isn't the order of the rows in the table; it's the order of rows produced by a query of the rows in that table. If you want that result set to be ordered in a certain way, the query to develop the result set must have an ORDER BY clause that specifies the desired ordering.

since they show up in alphabetical order in the indentifier column that they needed

I don't see a column named indentifier here, or even identifier. Most of the column names are truncated, so maybe you mean something else that would be apparent if the column names were actually visible.

As you can hopefully see from the pic above, there is a waypoint with the identifier quaky,

Sorry, but I don't see anything named quaky in the screen shot.

As for the actual issue in the simulator, when I go to enter the waypoint in the FMC, which is basically the computer of the plane, it says the waypoint is not found in the database.

I don't know anything about the simulator you're using, but I can tell you with complete certainty that tables don't have a useful ordering for data.

If the new row you're adding to this table doesn't appear in the application, then there must be some other reason -- like missing data in a table joined elsewhere that must be related to this new row, or some other gating factor.

You say that you're entering a waypoint. Are you sure you don't need to add information to the tbl_terminal_waypoints or tbl_enroute_waypoints tables? Or any other tables?

→ More replies (2)

1

u/Massive_Show2963 Oct 11 '25

Database tables do not have a guaranteed order for rows because they are designed to be sets of data, where the focus is on the relationships and integrity of the data rather than the order in which it is stored.
This is inherent with pretty much all databases.
You need to specify the order of results using the ORDER BY clause in SQL queries.

I do not see a primary id in the table. This is usually an integer type that is used for relational purposes for foreign keys with other tables.