For one of my iPhone apps I need to rename route points to match their order as they are inserted or deleted, I started with an easy WITH version:
And ran into the problem when testing on iOS8.1, obviously its version of sqlite didn't support WITH at that time.
Not that nicely looking, but I'm still committed to support iOS8 for a few more months. If you are puzzled by that LSSTRING part - that's just my macro for the LocalizableString as I only want to rename these points that are named automatically and surely I want to name them in the localized manner. Punto it is in Spanish (I hope) :). vY1 is a cryptic name for the order column :).
[NSString stringWithFormat:@"WITH wcte (id, wname) AS (SELECT w.id, '%@ ' || (SELECT COUNT(*) + 1 FROM waypoint WHERE vY1 < w.vY1) as wname FROM waypoint w) UPDATE waypoint SET \"name\" = (SELECT wname FROM wcte WHERE id = waypoint.id) WHERE \"name\" LIKE '%@ %%' OR \"name\" is null OR \"name\" = ''", LSSTRING(@"Point"), LSSTRING(@"Point")]
So here is the workaround solution for older sqlite versions:
[NSString stringWithFormat:@"UPDATE waypoint SET \"name\" = (SELECT '%@ ' || (SELECT COUNT(*) + 1 FROM waypoint w WHERE w.vY1 < w1.vY1) FROM waypoint w1 WHERE w1.id = waypoint.id) WHERE \"name\" LIKE '%@ %%' OR \"name\" is null OR \"name\" = ''", LSSTRING(@"Point"), LSSTRING(@"Point")]
Would not be publishing at all, but sqlite syntax sometime is surprising in what it can or can't do, so I thought I might save time to someone.
If you are into hiking, fishing, cycling or classic skiing here is the app link, it's free: https://itunes.apple.com/us/app/id1120906807