ふたつのテーブルをひとつにまとめたい！

Question

SQLiteでデータベースの勉強中です！

テーブルがふたつあって、それをIDを基準にしてまとめたいです。

↓テーブルA（こちらが親）

ID	item	price
1	りんご	300
2	いちご
3	バナナ	100
4	みかん

↓テーブルB（こちらが子ども）

ID	price
2	200
3	150

↓ほしいかたち

ID	item	price
1	りんご	300
2	いちご	200
3	バナナ	150
4	みかん

IDを基準にして、Bにある情報をAにまとめてしまいたい、というような感じです。
BのテーブルにあってAのテーブルにないようなIDはありません。
また、Bのpriceが正しいことがわかっているので、かぶるところがあったら上書きしたいです。

調べてみたところ、情報を更新するには UPDATE と JOIN を使えばいいようだと思っています。

https://www.dbonline.jp/sqlite/insert/index5.html

↑これをもとにして、条件のところに別のテーブルを使えたらいいかなと思っていますが、そのSQL文を教えていただけませんでしょうか…。

Comments

[firedfly]

こんにちは
・AとBをjoinした結果を表示したい
・AのpriceをBのpriceで更新（update）したい
どちらでしょうか。
また、レコードによってAにpriceが入っているけれどBに入っていないことはあるでしょうか。

[hacosato]

ご質問ありがとうございます！

> どちらでしょうか。
後者です！ UPDATEしたいです！

> レコードによってAにpriceが入っているけれどBに入っていないことはあるでしょうか。
あります！ 例のID=1がそうなってます！

Aのテーブルの情報が虫食いだらけでしかも信用できない中、
情報量は少ないけど確実なBのテーブルが入荷したぞ！という状況で、
すべての情報をAにまとめたい、というような感じです。

どうぞよろしくお願いします。

[firedfly]

なるほど。
Bにレコードがあれば、必ず正しいpriceが入っているということですね。

[hacosato]

大変遅くなりすみません！
それで合ってます！！

Answer 1

coalesce()を使用して、更新前の値を保持しながら、というのもありだと思います。

select a.*, coalesce(b.price, a.price) as Latest_price
from A LEFT JOIN B ON A.id = B.id

更新するなら、firedifyさんの方が効率はいいですけど、以下の様な方法もあります。

UPDATE A
SET price = coalesce((SELECT price FROM B WHERE id=A.id), price)

Comments

[hacosato]

ご回答ありがとうございました！

たしかに動いたのですが、りくつがどうもよくわからなかったです…。
coalesceをもっと時間かけてじっくりさわってみます〜。

Answer 2

こんにちは。
Bの内容でAをUPDATEしたいということで、他のDBでは次のSQLになります。

UPDATE A
LEFT JOIN B ON A.id = B.id
SET price = B.price

が、残念ながらSQLiteではUPDATEとJOINを同時に使うことはできません。
サブクエリでがんばりましょう。

UPDATE A
SET price = (SELECT price FROM B WHERE id = A.id)
WHERE EXISTS (SELECT price FROM B WHERE id = A.id)

Comments

[hacosato]

ありがとうございました！ お返事大変遅くなってしまいすみません…。

ご提示いただいたのを少し変えて、以下のようにしたら動きました〜。

UPDATE A SET price = (SELECT price FROM B WHERE ID = A.ID)
WHERE EXISTS (SELECT price FROM B WHERE ID = A.ID);

SETの直後のカラムの名前にはテーブルの名前いらないみたいでした…。どうして…。

よく読み返してみたら意味がわからないところはとくになかったので、
これイディオムとして口からスラスラ出るようにしたいと思います！

[firedfly]

おっと失礼、修正しておきました。
サブクエリを使えるとだいたいの作業はできます。

[hacosato]

ありがとうございます！ がんばります！

[hacosato]

古い回答にすみません。
先日はありがとうございました！
その後、自分で試してみたところ、

UPDATE A
SET price = (SELECT price FROM B WHERE id = A.id)
WHERE id = A.id;

という短い文で、やりたいことができている感じがしました。
EXISTSという部分はないとどういった弊害があるのでしょうか？

[firedfly]

こんにちは。

UPDATE A
SET price = (SELECT price FROM B WHERE id = A.id)

絞り込む必要なければWhere句は省いていいですが、
Bに対応するidがなかった場合にpriceにNULLが入ってしまわないでしょうか。

[hacosato]

firedflyさま
お返事ありがとうございます！

> Bに対応するidがなかった場合にpriceにNULLが入ってしまわないでしょうか。

おっしゃる通りでした????
EXISTSの必要性が理解できました…どうもありがとうございます！