Q&A
Closed
SQLiteでupdate文中にorder byを使おうと思ったのですが、SQLiteでは出来ないようです。特定のカラムの順序で並べたテーブルを更新する方法を教えてください。
下記画像が使用しているテーブルです（見づらくてすみません。）
具体的には、positionカラムの昇順で並べてから、0, 1, 2, と+1ずつ増やしてpositionカラムを更新したいです。
updateにorder byを指定するのは、MySQLでは出来るそうですが、SQLiteではできないということは調べて分かりました。
ご回答よろしくお願いいたします。
SQLiteで分析関数(window関数)が使えるバージョンでしたら以下のＳＱＬでどうでしょうか？
UPDATE texts AS m SET position =
(
SELECT Newposition - 1
FROM (SELECT _id
, ROW_NUMBER() OVER (order by position) AS Newposition
FROM texts
)
WHERE _id = m._id
);
参考までに分析関数部分のみの実行結果は以下の通りです。
SELECT _id
, ROW_NUMBER() OVER (order by position) AS Newposition
FROM texts
| _id | Newposition |
|---|---|
| 4 | 1 |
| 7 | 2 |
| 6 | 3 |
こちらでお試しできるようにしました
https://wandbox.org/permlink/Q40R24kkwipGRyHR
@kunusann
Questioner
詳しくないので、解釈から間違っている可能性もありそうなのですが、こういうやり方では解決にならないでしょうか?
テーブルtextsに対する下記の処理をSQLで記述します。
| _id | text | created_at | position | isChecked |
|---|---|---|---|---|
| 4 | s | 2020-10-28 13:35:35 | 0 | 1 |
| 6 | a | 2020-10-28 13:35:46 | 4 | 1 |
| 7 | k | 2020-10-28 13:50:33 | 2 | 0 |
| _id | text | created_at | position | isChecked |
|---|---|---|---|---|
| 4 | s | 2020-10-28 13:35:35 | 0 | 1 |
| 6 | a | 2020-10-28 13:35:46 | 2 | 1 |
| 7 | k | 2020-10-28 13:50:33 | 1 | 0 |
position列の昇順に、0で始まる連番を振り直します。
UPDATE texts AS m SET position = (
SELECT COUNT(*) - 1 FROM texts
WHERE position <= m.position
);
このSQL文はエラーせずに完了しますが、課題は達成できず、position列は0, 2, 2になります。
これは、条件に使用してるテーブル(m)を書き替えてしまうためです。
ソートしたテーブルを更新することで回避できれば良いのですが、SQLiteではUPDATEにORDER BYが使えないという問題が立ち塞がるわけですね。
少々トリッキーな方法ですが、テーブルにカラムtempを追加した上で、以下のSQLを実行するというのはいかがでしょうか。
UPDATE texts AS m SET temp = (
SELECT COUNT(*) - 1 FROM texts
WHERE position <= m.position
);
UPDATE texts SET position = temp;
@kunusann
Questioner