Q&A
1. sort_orderは何の順番ですか? nameの昇順ですか?
2. sort_orderだけでなくidも変更しますか?
| id | category | name | sort_order |
|---|---|---|---|
| 1 | fruit | apple | 1 |
| 2 | fruit | grape | 2 |
| 3 | fruit | orange | 3 |
| 4 | Vegetables | carrot | 4 |
| 5 | Vegetables | garlic | 5 |
とした場合、sort_orderには任意で数字を設定します。
createの時点で連番にしたものですが、後々insert intoで追加しようと思いましたが、
3と4の間に新データを追加したい。
この場合、sqlでそれ以降のデータのsort_orderを自動的に連番にすることは可能ですか？どう書きますか？
期待結果
| id | category | name | sort_order |
|---|---|---|---|
| 1 | fruit | apple | 1 |
| 2 | fruit | grape | 2 |
| 4 | fruit | peach | 3 |
| 3 | fruit | orange | 4 |
| 5 | Vegetables | carrot | 1 |
| 6 | Vegetables | garlic | 2 |
postgresです。
失礼しました、12:50追記ーーーーーーーーーーーーーーーーーーー
これは、カテゴリーの中でに限定することは可能ですか？
カテゴリーfruitの2と3の間に新データ(peach)を追加したい。
1 = そのデータをweb上で表示する際に参考にする順番です。
2 = いいえ。insertの時に自動で採番されます。
あくまでもこちらが任意で設定する値ですが、既に設定してある(連番に設定した。特別なことはしてない)ものに、追加で差し込みをしたいと。
2と3の間に、新しいデータを入れたい場合、このまま入れると 1 2 2 (3) 3 4 5 となりますよね。なので、後続の値を4 5 と自動でつけれたらなと思ったんです。
しかし、他の方の回答を見ていただければわかる通り、それほど自由度は高くないと。一つずつupdateもしくは +1 でしていくことが必要になるそうです。
これにいたっては今確認中です。
回答3件
0
sort_orderには任意で数字を設定します。
それ以降のデータのsort_orderを自動的に連番にすることは可能ですか？
設定するのか自動で行うかのどちらなのでしょう？
内容的にはcategory内での、name順をsort_orderで指定しているという事ですよね。
IDがserial値なら、category、sort_order、IDの順であれば良いように思います。
SQL
1order by category, sort_order Nulls Last, ID
この状態であれば追加したものでsort_orderがnullならば後方に来ます。
その上で、並べ替えたい順があるなら、sort_order を更新すれば良いでしょう。
追記
データ投入に於いて、追加でデータ投入した場合の、sort_orderの値設定という話なら、そもそも最初に投入したデータのsort_orderはどうやって決めたの？って話ですから、元になるデータを作成した上で、それを元に追加・更新という事になるかと思います。
投稿2021/06/08 04:44
編集2021/06/08 04:59
総合スコア25184
Nulls 〜 なんてオプションあるんですね。これは良いことを聞いた。
大抵こういうのって整数でnull許容せず重複出さないように制御する設計にしてましたけど、これはこれで使い道がありそう。
@m.ts10806さん
Nullsが指定できるものとそうでないものがあります。
以下参考
NULLと戯れる: ORDER BYとNULL(https://qiita.com/SVC34/items/c23341c79325a0a95979)
おお！ありがとうございます。
0
ベストアンサー
データを削除できるんだったら、
最初っからきれいに入れ直してもバチは当たらない気がするけど、
それでもデータを直したければ、
地道にUPDATE文を実行していけばいいと思う。
なお、SELECT文でのORDER BY句で決めた並び順が保てればいいんだろうから、
idの付け替えはしないようだし、
ふつうにUPDATE文やINSERT INTO文を、変更が必要なレコードidに対して行うだけですよ。
おすすめな方法として、トランザクション処理を加えます。
sql
1BEGIN; -- トランザクション開始 2 SELECT id, category, name, sort_order FROM テーブル名 ORDER BY category, sort_order; -- 変更前の状態を表示して記録 3 UPDATE テーブル名 SET name = 'carrot', sort_order = 1 WHERE id = 4; 4 INSERT INTO テーブル名(id, category, name, sort_order) VALUES (6, 'Vegetables', 'garlic', 2); 5 UPDATE テーブル名 SET category = 'fruit', name = 'peach', sort_order = 3 WHERE id = 4; 6 SELECT id, category, name, sort_order FROM テーブル名 ORDER BY category, sort_order; -- 変更後の状態も表示して目視で比較する 7ROLLBACK; -- これで変更はなかったことになる（問題なければCOMMITに変える）
これで、変更前のテーブルと、変更後のテーブルを表示するので、
意図した内容になるのを確認したら、ROLLBACKをCOMMITに直して実行すれば反映されます。
ちまちまUPDATE文やINSERT INTO文やDELETE文を書くのが面倒なら、
一発truncate table文をやって、
COPY文で全レコード投入しなおせばいいです。
実行するSQL文をテキストファイルで作っておく前提で。
serial型の話だと思って、勘違いの回答をしてしまいました。
恥ずかしいけど残しておきます。
serial型って、テーブル名とカラム名を伴ったシーケンスが定義されているので、
setval()を使って値を再設定すればいいです。
SELECT setval('foo', 42, false); 次のnextvalは42を返す
投稿2021/06/08 03:44
編集2021/06/08 05:12
退会済みユーザー
総合スコア0
途中に割って入れたあと振り直したいという要件に見えたのですが…
はい。updateで変更すればいいんですが、これ数が増えた場合困るなと思って…。
数が幾ら増えてもinsert１件なら発行するupdate文は1つです。
考えるのが面倒なら、truncateしてinsert intoもしくはcopyで。どうせ初期データ投入の段階でしょ、絶対レコード削除できないシビアな状況でなければ、ラクにやっていいんじゃないかな。
絶対削除できないものなので、自動でできるものなのかと質問しました。
>地道にUPDATE文を実行
例えば100まであったとしたら、1つインサート後ほかの方が回答してくださっている、
set sort_order=sort_order+1 where sort_order > ?
こいつの、whereにカテゴリーを指定してあげるだけでよさそうですか？
こちらの回答ご覧なりましたか？これではなにか懸念点があるので、地道にとおっしゃいましたか？
心配なら使うな、心配ならテストして使え、それだけです。
たまたまそのシチュエーションでの番号の振り直しには、たまたま使えるでしょう。
それを確認するためにも、UPDATE文の前後にSELECT文を置いて、変更後の様子を観察できるようにします。
そのために、BEGIN～ROLLBACKというやり方も示しました。
ROLLBACKすれば元に戻りますので、いくらでもSQLを試せます。
質問者さん
私も近いことを書いています。読まれましたか？心配ならupdateと同じ条件でselectを。
100%一発でなんて中々熟練者でもありえないし、なんなら先にバックアップ取ってもいいんじゃないでしょうか。
ちょっと一旦落ち着いて考えますね。ありがとうございます。
0
そのような高度なことは自動ではできません。
insert後にそのinsertしたsort_order以上の番号に1を足すUPDATE文を発行してください。
set sort_order=sort_order+1 where sort_order > ?
投稿2021/06/08 03:44
総合スコア80850
失礼しました。大事なことを書き忘れていました。追記しましたご確認お願いします。
方針は変わりません。
where条件足せば良いです。
不安ならいきなりupdateかけず、先に対象のデータが取れるか同じwhere使ったselectで確認すると良いです。
これはupdateに限らずdeleteでも使える考え方。
あなたの回答
tips
プレビュー
