ラベル sql の投稿を表示しています。 すべての投稿を表示
ラベル sql の投稿を表示しています。 すべての投稿を表示

2026年4月26日日曜日

SQLite 3 で月単位の計算をする

概要

SQLite にはデータ型として日付型がないため、日付の計算は少し特殊な書き方をする必要がある。
この記事では「月」単位の計算についてまとめる。

実行例

環境
  • Windows 10
  • sqlite Command-Line Shell ver 3.51.3
単純な日付型の加減算では計算出来ない

date()や datetime()の加減算では計算出来ず、以下のような結果になってしまう。
どうやら最初の「年」の箇所だけを数値として取り出して計算しているため、このままでは月単位の計算はできない。 


sqlite> select date('2026-05-01') - date('2026-03-01');
0
sqlite> select date('2025-01-31') - date('2022-12-31');
3
sqlite> select date('2026-05-01') - 1;
2025
月を加算/減算する

月単位の加算/減算をする場合はdate()や datetime()の引数にNNN monthsを追加する。 例えば１年後を計算したければ+1 monthsを追加する。 


sqlite> select date('2026-04-01', '+1 months');
2026-05-01

sqlite> select date('2026-04-01', '-1 months');
2026-03-01

sqlite> select datetime('2026-04-01 09:00:00', '+1 months');
2026-05-01 09:00:00

sqlite> select datetime('2026-04-01 09:00:00', '-1 months');
2026-03-01 09:00:00

例えばこのようなテーブルの各行の１か月前、１か月後を計算するには以下のようなSQLになる。

user_table
user_name expiration_date
hoge1 2026-04-15
hoge2 2026-12-31
hoge3 2024-02-29 

select
    user_name
    , expiration_date
    , date(expiration_date, '+1 months') as next_month
    , date(expiration_date, '-1 months') as prev_month
from
    user_table
;
結果
user_name expiration_date next_month prev_month
hoge1 2026-04-15 2026-05-15 2026-03-15
hoge2 2026-05-31 2026-07-01 2026-05-01
hoge3 2024-02-29 2024-03-29 2024-01-29

5月31日に +1か月 した場合、6月31日は存在しないため、オーバーフローして7月1日が返されるっぽい。
1か月前も同じような理屈になり、4月30日が返されるわけではないので注意が必要。

２つの日付間の月数を計算する

大きく分けて2パターン。

１つ目は、strftime()関数を使って年と月の部分を取り出して計算する方法。
２つ目は、julianday()関数を使って日数に変換してから月数に変換する方法。

１つ目の方法は、単純に年と月の部分を取り出して計算する方法。
年部分の差に12（か月）をかけ、月の差を足せばいい。 


sqlite> select
   ...>     ( strftime('%Y', '2026-01-05') - strftime('%Y', '2025-12-30') ) * 12
   ...>     + ( strftime('%m', '2026-01-05') - strftime('%m', '2025-12-30') )
   ...> ;
1

ただし、この方法は単純に年の部分だけを取り出して計算するため、日数が考慮されない。
例えば上記のSQLでは、2026年1月5日と2025年12月30日の差は1か月と計算されるが、実際には6日の差しかない。

２つ目の方法は、日付を日数に変換してから月数に変換する方法。
例えば以下のようなSQLになる。 


sqlite> select (julianday('2026-01-05') - julianday('2025-12-30')) / 30;
0.2

この方法では、日付を日数に変換してから月数に変換するため、日数が考慮される。
例えば上記のSQLでは、2026年1月5日と2025年12月30日の年数の差は0.2か月と計算される。 （厳密に31日の月や2月の日数を計算しようとするとさすがに面倒なので、ここでは1か月=30日で計算している）
必要に応じて小数点以下を切り捨てるなどの処理を行うと良い。 


sqlite> select cast((julianday('2026-01-05') - julianday('2025-12-30')) / 30 as int);
0

参考URL

Posted by at
Labels: , , ,

2026年4月18日土曜日

SQLite 3 で年数単位の計算をする

概要

SQLite にはデータ型として日付型がないため、日付の計算は少し特殊な書き方をする必要がある。
この記事では「年」単位の計算についてまとめる。

実行例

環境
  • Windows 10
  • sqlite Command-Line Shell ver 3.51.3
単純な日付型の加減算では計算出来ない

date()や datetime()の加減算では計算出来ず、以下のような結果になってしまう。
どうやら最初の「年」の箇所だけを数値として取り出して計算しているらしいが、正直モヤっとする。 


sqlite> select date('2026-05-01') - date('2026-03-01');
0
sqlite> select date('2025-01-31') - date('2022-12-31');
3
sqlite> select date('2026-05-01') - 1;
2025
年を加算/減算する

上記の式で納得できるのであれば、それでOK。

ただ、コードの保守性や引継ぎを考えると 「ここは年の計算をしている」ことがコードからわかることがベスト。

年単位の加算/減算をする場合はdate()や datetime()の引数にNNN yearsを追加する。 例えば１年後を計算したければ+1 yearsを追加する。 


sqlite> select date('2026-04-01', '+1 years');
2027-04-01

sqlite> select date('2026-04-01', '-1 years');
2025-04-01

sqlite> select datetime('2026-04-01 09:00:00', '+1 years');
2027-04-01 09:00:00

sqlite> select datetime('2026-04-01 09:00:00', '-1 years');
2025-04-01 09:00:00

例えばこのようなテーブルの各行の１年後、１年前を計算するには以下のようなSQLになる。

user_table
user_name expiration_date
hoge1 2026-04-15
hoge2 2026-12-31
hoge3 2024-02-29 

select
    user_name
    , expiration_date
    , date(expiration_date, '+1 years') as next_year
    , date(expiration_date, '-1 years') as prev_year
from
    user_table
;
結果
user_name expiration_date next_year prev_year
hoge1 2026-04-15 2027-04-15 2025-04-15
hoge2 2026-12-31 2027-12-31 2025-12-31
hoge3 2024-02-29 2023-03-01 2025-03-01

うるう年の2月29日に年単位の加算・減算をした結果、 その年がうるう年でない場合は2月28日ではなく 3月1日になるので注意すること。

２つの日付間の年数を計算する

大きく分けて2パターン。

１つ目は、strftime()関数を使って年の部分だけを取り出して計算する方法。
２つ目は、julianday()関数を使って日数に変換してから年数に変換する方法。

１つ目の方法は、単純に年の部分だけを取り出して計算する方法。
例えば以下のようなSQLになる。 


sqlite> select strftime('%Y', '2026-05-01') - strftime('%Y', '2025-11-01') as span_year;
1

ただし、この方法は単純に年の部分だけを取り出して計算するため、月や日が考慮されない。
例えば上記のSQLでは、2026年5月1日と2025年11月1日の年数の差は1年と計算されるが、実際には6ヶ月の差しかない。

２つ目の方法は、日付を日数に変換してから年数に変換する方法。
例えば以下のようなSQLになる。 


sqlite> select (julianday('2026-05-01') - julianday('2026-03-01')) / 365.25;
0.167008898015058

この方法では、日付を日数に変換してから年数に変換するため、月や日が考慮される。
例えば上記のSQLでは、2026年5月1日と2026年3月1日の年数の差は0.16年と計算される。 （厳密にうるう年を計算しようと思うと大変なのでここでは0.25日を使う。）
必要に応じて小数点以下を切り捨てるなどの処理を行うと良い。 


sqlite> select cast((julianday('2026-05-01') - julianday('2026-03-01')) / 365.25 as int);
0
sqlite> select cast((julianday('2026-05-31') - julianday('2025-04-30')) / 365.25 as int);
1

参考URL

Posted by at
Labels: , , ,

2023年1月30日月曜日

SQLite 3 で二つの日付間の日数を計算する

概要

SQLite ではdate関数等の結果を加減算しても期待するような結果にはならない。 二つの日付間の日数を計算するにはユリウス通日を利用する必要がある。

実行例

環境
  • Windows 10
  • sqlite Command-Line Shell ver 3.40.1
単純な日付型の加減算では計算出来ない

date()や datetime()の加減算では計算出来ず、以下のような結果になる。 


sqlite> select date('2023-01-31') - date('2022-12-31');
1
sqlite> select datetime('2023-01-31 09:00:00') - datetime('2022-12-31 09:00:00');
1
期間(日数)を計算するにはユリウス通日を使用する

    sqlite> select julianday('2023-01-31') - julianday('2022-12-31');
    31.0

このままだと小数点が邪魔なため、cast()関数で整数に変換する。


    sqlite> select cast(julianday('2023-01-31') - julianday('2022-12-31') as integer);
    31

参考URL

Posted by at
Labels: , , , ,

2022年3月5日土曜日

SQLite3 年の初日を取得する (start of year)

概要

指定した日時からその年の初日(1月1日)を取得する方法。
文字列を切り貼りするよりはスマートに記述することが出来る。

構文

「date()」もしくは「datetime()」の引数に「start of year」を追加すると その月の初日を取得することができる。
「start of year」は大文字と小文字どちらでもいい。

「datetime()」の引数に「start of year」を指定した場合、 結果の時間部分は「00:00:00」になることに注意すること。 


date(time-value, 'start of year')
datetime(time-value, 'start of year')

日時として指定出来る文字列(time-value)については以下を参照のこと。

実行例

環境
  • Windows 10 64bit
  • SQLite3 ver.3.37.0

sqlite> select date('2022-03-05', 'start of year');
2022-01-01

sqlite> select datetime('2022-03-05 09:00:00', 'start of year');
2022-01-01 00:00:00

sqlite> select date('now', 'start of year');
2022-01-01

sqlite> select datetime('now', 'start of year');
2022-01-01 00:00:00

sqlite> select date(datetime('2022-03-05 09:00:00'), 'start of year');
2022-01-01

sqlite> select datetime(date('2022-03-05'), 'start of year');
2022-01-01 00:00:00

外部参考サイト

Posted by at
Labels: , ,

2022年2月19日土曜日

SQLite3 年内日を求める(sfrftime('%j','yyyy-MM-dd'))

概要

strftime()を使用すれば、指定した日付が年内の何日目かを取得できる。

構文

strftime()は第一引数に日時から取得する要素の変数を、第二引数に実際の日時を指定することで 日時を自由な文字列フォーマットに変換して出力することができる。 


strftime(format, datetime)

日時として指定出来る文字列はdate()やdatetime()に指定する文字列と同じ。

年内日を取得するにはstrftime()の第一引数に「%j」を指定する。
大文字の「%J」ではなく、小文字の「%j」を指定すること。

実行例

環境
  • Windows 10 64bit
  • SQLite3 ver.3.37.0

sqlite> select strftime('%j', '2020-12-30');
365
sqlite> select strftime('%j', date('2020-12-30'));
365
sqlite> select strftime('%j', datetime('2020-12-30'));
365
sqlite> select strftime('%j', '2020-12-31');
366
sqlite> select strftime('%j', '2021-01-01');
001
sqlite> select strftime('%j', '2021-01-31');
031
sqlite> select strftime('%j', '2021-02-01');
032

外部参考サイト

Posted by at
Labels: , ,

2022年2月12日土曜日

SQLite3 年内週を取得する (strftime('%W', 'yyyy-MM-dd'))

概要

strftime()を使用すれば、指定した日付が年内の何週目かを取得できる。

1月1日が必ず第1週になるわけではないことに注意すること。

構文

strftime()は第一引数に日時から取得する要素の変数を、第二引数に実際の日時を指定することで 日時を自由な文字列フォーマットに変換して出力することができる。 


strftime(format, datetime)

日時として指定出来る文字列はdate()やdatetime()に指定する文字列と同じ。

年内週を取得するにはstrftime()の第一引数に「%W」を指定する。 小文字の「%w」では無く大文字の「%W」を指定すること。

実行例

環境
  • Windows 10 64bit
  • SQLite3 ver.3.37.0

sqlite> select strftime('%W', '2022-02-09');
06
sqlite> select strftime('%W', date('2022-02-09'));
06
sqlite> select strftime('%W', datetime('2022-02-09'));
06

月曜日が基準になっている

SQLiteでの年内週の数え方は1月1日以降の月曜日の数となる。

以下は2018年と2019年の比較。 月曜日で+1されていることがわかる。

2018年
1月
日曜日 月曜日 火曜日 水曜日 木曜日 金曜日 土曜日
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31 

sqlite> select strftime('%W', '2018-01-01');
01
sqlite> select strftime('%W', '2018-01-02');
01
sqlite> select strftime('%W', '2018-01-07');
01
sqlite> select strftime('%W', '2018-01-08');
02
sqlite> select strftime('%W', '2018-01-09');
02
sqlite> select strftime('%W', '2018-01-14');
02
sqlite> select strftime('%W', '2018-01-15');
03
sqlite> select strftime('%W', '2018-01-16');
03
2019年
1月
日曜日 月曜日 火曜日 水曜日 木曜日 金曜日 土曜日
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31 

sqlite> select strftime('%W', '2019-01-01');
00
sqlite> select strftime('%W', '2019-01-06');
00
sqlite> select strftime('%W', '2019-01-07');
01
sqlite> select strftime('%W', '2019-01-08');
01
sqlite> select strftime('%W', '2019-01-13');
01
sqlite> select strftime('%W', '2019-01-14');
02
sqlite> select strftime('%W', '2019-01-15');
02

外部参考サイト

Posted by at
Labels: , ,

2022年2月5日土曜日

SQLite3 日付から曜日を取得する (strftime('%w', 'yyyy-MM-dd'))

概要

SQLite3で日付から曜日を取得するにはstrftime()関数を使用する。

構文

strftime()は第一引数に日時から取得する要素の変数を、第二引数に実際の日時を指定することで 日時を自由な文字列フォーマットに変換して出力することができる。 


strftime(format, datetime)

日時として指定出来る文字列はdate()やdatetime()に指定する文字列と同じ。

曜日を取得するにはstrftime()の第一引数に「%w」を指定する。 返り値は0から6までの値となり、これが日曜日～月曜日を指す。

返り値 曜日
0 日曜日
1 月曜日
2 火曜日
3 水曜日
4 木曜日
5 金曜日
6 土曜日

実行例

環境
  • Windows 10 64bit
  • SQLite3 ver.3.37.0

sqlite> select strftime('%w', '2022-01-01');
6
sqlite> select strftime('%w', '2022-01-02');
0
sqlite> select strftime('%w', '2022-01-03');
1
sqlite> select strftime('%w', '2022-01-04');
2
sqlite> select strftime('%w', '2022-01-05');
3
sqlite> select strftime('%w', '2022-01-06');
4
sqlite> select strftime('%w', '2022-01-07');
5

曜日の名前を取得したい

strftime()関数で曜日が数字として取得出来るので、 これを曜日名に直すにはCASE文などを利用して条件分岐してやればいい。

なお、strftime()の戻り値は数値型ではなく文字列型として比較すること。

sample_date
id str_date
A 2022-01-01
B 2022-01-02
C 2022-01-03
D 2022-01-04
E 2022-01-05
F 2022-01-06
G 2022-01-07 

sqlite> .mode box
sqlite> select
   ...>     id
   ...>     , case strftime('%w', str_date)
   ...>         when '0' then 'Sunday'
   ...>         when '1' then 'Monday'
   ...>         when '2' then 'Tuesday'
   ...>         when '3' then 'Wednesday'
   ...>         when '4' then 'Thursday'
   ...>         when '5' then 'Friday'
   ...>         when '6' then 'Saturday'
   ...>         else 'undefined'
   ...>     end as weekday
   ...> from
   ...>     sample_date
   ...> ;
┌────┬───────────┐
│ id │  weekday  │
├────┼───────────┤
│ A  │ Saturday  │
│ B  │ Sunday    │
│ C  │ Monday    │
│ D  │ Tuesday   │
│ E  │ Wednesday │
│ F  │ Thursday  │
│ G  │ Friday    │
└────┴───────────┘

外部参考サイト

Posted by at
Labels: , , ,

2022年1月22日土曜日

SQLite3では日時は文字列型で保存する

概要

SQLite3のカラムのデータ型にはDate型やDatetime型が無いので、 データベースには文字列型として登録する。

日付の大小を比較するのであれば文字列のまま比較すればいいが、 日付の計算をしたい場合はdate()やdatetime()といった関数を使用する。

実行例

環境
  • Windows 10
  • sqlite3 (3.27.0)

現在日時をデータベースに登録する

  1. 日付を登録するカラムは「text」型にしておく。
  2. 本日日付を「date('now')」で取得し、新規挿入する。
  3. 現在時間を「time('now')」で取得し、新規挿入する。
  4. 現在日付、時間を「datetime('now')」で取得し、新規挿入する。
  5. selectで現在日時が登録されていることが確認できる。

sqlite> -- # 1.
sqlite> create table sample_date (
   ...>     test_date text
   ...> );

sqlite> -- # 2.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     date('now')
   ...> );

sqlite> -- # 3.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     time('now')
   ...> );

sqlite> -- # 4.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     datetime('now')
   ...> );

sqlite> -- # 5.
sqlite> select test_date from sample_date;
2022-01-18
13:47:39
2022-01-18 13:47:40

特定の日付を登録する

文字列として登録すればいいのだが、後で「date()」「datetime()」で使用するために SQLite3の日時フォーマットにしたがった文字列にしたほうがいい。

日時フォーマットについては以下の記事を参照のこと。

スラッシュ区切りなんかで登録してしまうと後々面倒なことになる。

以下は特定の日時を登録する実行例。

  1. 日付を登録するカラムは「text」型にしておく。
  2. 「2022年1月1日」を新規挿入する。
  3. 「9時9分9秒」を新規挿入する。
  4. 「2022年1月1日9時9分9秒」を新規挿入する。

sqlite> -- # 1.
sqlite> create table sample_date (
   ...>     test_date text
   ...> );

sqlite> -- # 2.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '2022-01-01'
   ...> );

sqlite> -- # 3.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '09:09:09'
   ...> );

sqlite> -- # 4.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '2022-01-01 09:09:09'
   ...> );

sqlite> -- # 5.
sqlite> select test_date from sample_date;
2022-01-01
09:09:09
2022-01-01 09:09:09

日時を比較する

日時を比較する場合、フォーマットがあっていれば文字列として比較することもできる。

万全を期すのであればdate()やdatetime()といった関数で日付型にしてから比較したほうがいい。
「YYYY-MM-DD HH:MM:SS」と「YYYY-MM-DDTHH:MM:SS」といったフォーマットが違う日付データを 文字列として比較すると、意図しない結果になることがある。

以下は日時比較の実行例

  1. フォーマットが同じなら文字列として比較出来る。
  2. date()やdatetime()関数で変換してからも比較出来る。
  3. フォーマットが違う場合はdate()やdateitme()関数を使うこと。

sqlite> -- # 1.
sqlite> select '2022-01-01 09:00:00' > '2022-01-01 08:59:59';
1
sqlite> select '2022-01-01 09:00:00' > '2022-01-01 10:01:01';
0
sqlite> select '2022-01-01 09:00:00' = '2022-01-01 09:00:00';
1

sqlite> -- # 2.
sqlite> select datetime('2022-01-01 09:00:00') > datetime('2022-01-01 08:59:59');
1

sqlite> -- # 3.
sqlite> select '2022-01-01 09:00:00' > '2022-01-01T08:59:59';
0
sqlite> select datetime('2022-01-01 09:00:00') > datetime('2022-01-01T08:59:59');
1

外部参考サイト

Posted by at
Labels: , ,

2020年3月13日金曜日

SQLite 3 insert時にキーが重複していたら無視する (insert or ignore)

概要

insert or ignore 構文を使う方法のメモ

insert or ignore を使うと Primary key や Unique で 重複するレコードを挿入しようとした場合、 以下のような処理分岐をしてくれる。

重複するレコードがある場合 何もしない
重複するレコードがない場合 そのまま「新規挿入(insert)」

環境

  • Windows 10
  • sqlite3 (3.30.0) Command-Line Shell

insert or ignore 構文の例

サンプルテーブル(product)
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)

キーの重複が無ければ、通常通り追加する


sqlite> insert or ignore into product
          ( id  ,name     ,quantity ,remark )
        values
          ( 4   ,'carrot' ,30       ,''     )
        ;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)
4 carrot 30

キーの重複があった場合、無視する


sqlite> insert or ignore into product
          ( id ,name     ,quantity ,remark )
        values
          ( 3  ,'carrot' ,30       ,''     )
        ;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)

ignore 文にしなかった場合、エラーになる


sqlite> insert into product
          ( id ,name     ,quantity ,remark )
        values
          ( 3  ,'carrot' ,30       ,''     )
        ;

Error: table product has no column named id

複数データを追加する場合、キーの重複が無いデータだけ登録される


sqlite> insert or ignore into product
          ( id  ,name, quantity ,remark )
        values
          (   3 ,'carrot'  ,30  ,''     )
          , ( 4 ,'apple'   ,40  ,''     )
          , ( 5 ,'spinach' ,130 ,''     )
        ;
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)
4 apple 40
5 spinach 130

参考・関連 URL

Posted by at
Labels: , ,

2019年8月1日木曜日

SQLite3 SelectしたデータでUpdateする(～ver 3.29.0)

概要

select したデータを使って update するためのSQL。 select と update を別々に実行するのではなく、同時に実行できる。

2020.10.16 追記

バージョン 3.33.0 で「update～from」構文がサポートされた。 より簡単にselectしたデータでupdateすることが可能になっている。

詳しくは以下のURLより。

SQLite3 Selectした結果でUpdateする (update from)
UPDATE FROM (英語) 公式のUPDATE～FROMに関するドキュメント

以下はver 3.33.0 未満で作業したい場合のコマンド例

環境

  • Windows 10 64bit
  • SQLite3 (3.24.0) Command-Line Shell

insert ～ select ～ on conflict

selectした結果を使ってupsertする手順のうち、updateのみを行う。
SQLite3 でレコードがあれば更新、なければ新規挿入する (on conflict)

以下の点、注意。

  • 文法の都合上「where true」が必要
  • 主キーなどが重複した場合、select の結果はexcludedという一時テーブルに格納される。
    update する際はexcludedのデータを利用する。
  • 主キーが重複しないレコードは新規挿入になってしまうため、 updateだけを行いたい場合はselect文で調整する。 


inert into
    table_01
select
    *
from
    table_02
where true
on conflict(column01)
do update
    set
        column_02 = excluded.colum_02
        , column_03 = excluded.column_03
;

update ～ select

サブクエリでselect したデータを使って update する。
ポイントは

  • サブクエリのWhere句で二つのテーブルのキーを一致させる
  • メインクエリのWhere句で対象のレコードを指定する


update
    table_01
set
    column_02 = (
        select
            table_02.column05
        from
            table_02
        where
            table_02.column04 = table_01.column_01
    )
where
    table_01.column_01 = XXXX
;

参考URL

Posted by at
Labels: , , , ,

2019年7月25日木曜日

SQLite3 Selectしたデータを他のテーブルに挿入(insert ～ select, replace ～ select)

概要

selectしたデータを使ってinsert , replaceするためのSQL。 selectとinsertを別々に実行するのではなく1文で済ませられる。 タイトルには「他のテーブルに」と入れたけど、同じテーブルでもOK。 (そんな機会はほぼないはず・・・)

以下のような機会に使う。

  • CSVファイルを一時テーブルにインポートした後、 本番テーブルに取り込む
  • マテリアライズドビューのようなものを作る
  • テーブル間でデータをコピーする

insert ～ select

selectした結果をinsertする。
selectするカラム・型は挿入するテーブルのカラム・型と揃えること。

例. 「table_02」のデータを「table_01」に全てコピーする

insert into
    table_01
select
    *
from
    table_02
;
例. カラムや条件を指定してselectした結果をinsertする

insert into
    table_01 (
        column_01
        , column_02
        , column_03
    )
select
    column_05
    , column_06
    , column_07
from
    table_02
where
    column_05 = XXXX
;

ユニークキーが重複している場合はエラーになるので、 必要があればあらかじめチェックしておくこと。

重複していない行のみをinsertしたい場合は以下参照のこと。
SQLite 3 INSERT時にキーが重複していたら挿入しないようにする

replace ～ select

selectした結果でreplaceする。
ユニークキーが重複している場合は置換される。

例.「table02」のデータを「table01」にマージする

replace into
    table_01
select
    *
from
    table_02
;
例. カラムや条件を指定してselectした結果でreplaceする

replace into
    table_01 (
        column_01
        , column_02
        , column_03
    )
select
    column_05
    , column_06
    , column_07
from
    table_02
where
    column_05 = XXXX
;

replace構文については以下を参照のこと。
SQLite3 でレコードがあれば置換、なければ新規挿入する (replace)

selectしたデータでupdateをしたい場合は以下を参照のこと。
SQLite3 Select した結果でUpdateする(update from)

外部参考サイト

Posted by at
Labels: , , ,

2019年6月14日金曜日

SQLite3 でレコードがあれば置換、なければ新規挿入する (replace)

概要

replace (insert or replace) 構文を使う方法のメモ。

replace を使うと Primary key や Unique で 重複するレコードを挿入しようとした場合、 以下のような処理分岐をしてくれる。

重複するレコードがある場合 既存のレコードを「削除(delete)」 してから 「新規挿入(insert)」
重複するレコードがない場合 そのまま 「新規挿入(insert)」

注意

replaceで重複したキーがあった場合の処理は「削除(delete)」「新規挿入(insert)」であり、 「更新(update)」ではない。 カラムを指定しなければデフォルト値が入るし、NOT NULL制約にもひっかかる。

カラムを指定して「更新(update)」したい場合は replaceではなくupsertを使用する。
SQLite3 で upsert する

環境

  • Windows 10
  • SQLite3 (3.24.0) Command-Line Shell

replace 構文の例

サンプルテーブル(product)
id
primary key
autoincrement 		name
not null 		price
default 0 		category
1 tomato 100
2 potato 120
3 pumpkin 50 (Comment...)

replace(insert or replace) の実行例

以下はidが重複するデータと重複しないデータを挿入するSQL。 「insert or replace」の部分は「replace」だけでもOK。 


insert or replace into product (
    id
    , name
    , quantity
    , remark
)
values (
    2
    , 'potato'
    , 150
    , ''
),(
    4
    , 'carrot'
    , 100      
    , 'Prefecture: XX'
);
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 150
3 pumpkin 50 (Comment...)
4 carrot 100 (Prefecture : XX)

カラムを指定しないとデフォルト値になる

delete > insert であり、updateではないことを留意する。 


replace into product (
    id
    , name
)
values (
    2
    , 'potato'
);
結果
id
primary key
autoincrement 		name
not null 		quantity
default 0 		remark
1 tomato 100
2 potato 0
3 pumpkin 50 (Comment...)

not null 制約にも引っかかる


replace into product (
    id
    , quantity
)
values (
    2
    , 200
);

結果 


Error: NOT NULL constraint failed: product.name
カラムを指定してUPDATEする場合はUPSERTを利用する。
SQLite 3 で upsert する

参考・関連 URL

Posted by at
Labels: , , ,
登録: 投稿 (Atom)