◆ mysql (MariaDB) と postgresql で書き方違う

たまに SQL 使ったら
update ta as a
set
str1 = (select text1 from tc as c where c.id = (select cid from tb as b where b.id = a.bid)),
str2 = (select text2 from tc as c where c.id = (select cid from tb as b where b.id = a.bid)),
str3 = (select text3 from tc as c where c.id = (select cid from tb as b where b.id = a.bid))
where
a.id > 1000

こんな SQL が出来上がりました


テーブルは ta, tb, tc の 3 つです
ta
id
bid
str1
str2
str3

tb
id
cid
nanika
sonota

tc
id
text1
text2
text3

構造は tc のレコードに複数の tb のレコードが対応していて さらに tb のレコードには ta のレコードが複数 となっています
こういう感じ
{
    tc: {
        id: 1,
        tb: [
            {
                id: 1,
                nanika: "",
                sonota: "",
                ta: [
                    {
                        id: 1,
                        str1: 1,
                        str2: "",
                        str3: "",
                    }
                ]
            }
        ],
        text1: "",
        text2: "",
        text3: "",
    }
}

これで それぞれの ta に対して
str1 に tc の text1
str2 に tc の text2
str3 に tc の text3 という値を入れたいです

問題は
str1 = (select text1 from tc as c where c.id = (select cid from tb as b where b.id = a.bid)),
str2 = (select text2 from tc as c where c.id = (select cid from tb as b where b.id = a.bid)),
str3 = (select text3 from tc as c where c.id = (select cid from tb as b where b.id = a.bid))

このほぼ同じ文の繰り返し

デキる RDB はオプティマイザがとても賢くて適当に書いてもちゃんと書いても explain でみるとだいたい同じ感じの動きになってくれます

最近は変わってるかもしれませんが昔調べたときでは postgresql がすごくそのあたりが優れていて mariaDB も mysql から比べるとよくなっていて mysql はダメダメでした

そのせいで postgresql は初心者に優しく mysql は詳しい知識がないと能力を活かせない上級者向けとか言われてましたし

話を戻して この SQL
試してませんが 内部でキャッシュするなり最適化してくれていたとしても 書くのも見るのもイヤです
もうちょっと綺麗にしたい!!

どうにかできないか考えてみると
思いつくのは JOIN
やっぱり JOIN させたいですよね

UPDATE 文自体はほとんど使うことがなかった上に JOIN したいこともなかったので調べてみると できるみたい

mysql (maria) の場合は
update ta as a
left join tb as b
on a.bid = b.id
left join tc as c
on b.cid = c.id
set
str1 = text1,
str2 = text2,
str3 = text3
where
a.id > 1000

postgresql の場合は
update ta as a
set
str1 = text1,
str2 = text2,
str3 = text3
from tb as b
left join tc as c
on b.cid = c.id
where
a.id > 1000
and
a.bid = b.id

UPDATE 文くらい統一してほしい気もしますが いつものことと諦めます

mysql (maria) では SET の前に JOIN を書けるようです

逆に postgresql では SET のあとに FROM 句を書いてそこに JOIN するようです
UPDATE で指定したテーブルとは WHERE で関連付けます

FROM なしで直接 UPDATE 対象と JOIN できる mysql (maria) のほうが書きやすいです


調べていて思ったことですが 今になっても mysql のリファレンスは読む気が起きない見づらさで postgresql はわかりやすくて読む気の出るリファレンスです
つい余計なページも見てたくらい……

この辺も postgresql が初心者優しいと言われるところでしょうか



ところで テーブル作ってるときに 間違って逆の
{
    ta: {
        id: 1,
        tb: [
            {
                id: 1,
                tc: [
                    {
                        id: 1,
                        text1: 1,
                        text2: "",
                        text3: "",
                    }
                ]
            }
        ],
        str1: "",
        str2: "",
        str3: "",
    }
}

こういう構造にしてしまいました

テーブルのカラムだと
a
id
str1
str2
str3

b
id
aid
nanika
sonota

c
id
bid
text1
text2
text3

せっかくなのでこっちでもやってみました


まずはサブクエリを使う方法
update ta as a
set
str1 = (select text1 from tc as c where c.bid = (select b.id from tb as b where b.aid = a.id)),
str2 = (select text2 from tc as c where c.bid = (select b.id from tb as b where b.aid = a.id)),
str3 = (select text3 from tc as c where c.bid = (select b.id from tb as b where b.aid = a.id))
where
a.id > 1000

これはサブクエリが複数の結果を返すので mysql (maria) も postgresql もエラーになります
サブクエリを 「=」 で扱うときはこういうのがちょっと扱いづらいです
かといって複数返ってきたらどうすればいいのかと言われたら……エラーかなぁ

では次に JOIN でやってみます

[mysql (maria)]
update ta as a
left join tb as b
on a.id = b.aid
left join tc as c
on b.id = c.bid
set
str1 = text1,
str2 = text2,
str3 = text3
where
a.id > 1000

[posrgresql]
update ta as a
set
str1 = text1,
str2 = text2,
str3 = text3
from tb as b
left join tc as c
on b.id = c.bid
where
a.id > 1000
and
a.id = b.aid

どちらも正常にクエリが完了します
複数行になるところは最初のものが使われます

例えば ta, tb, tc のレコードの関係がこんな場合です

ta.id -> tb.id
1001: 1
1002: 2
1003: 3, 4

tb.id -> tc.id
1: 1
2: 2, 3
3: 4
4: 5, 6

tb を通して ta から tc だと

ta.id -> tc.id
1001: 1
1002: 2, 3
1003: 4, 5, 6

1002 には tc の 2 と 3 が対応します
2 のほうが先にあるので tc の id が 2 のデータが使われて str1 がセットされます

1003 のほうは 4 が対応するので tc の id が 4 のデータが使われます

order by でどっちが先か変わりそうですし ちょっと怖いですね

間違った上書きが許されないなら サブクエリのエラーが出てくれる方が安心できそうです


ところで使ったバージョンはこうなっています
MariaDB
Ver 15.1 Distrib 10.1.20-MariaDB

PostgreSQL
9.5.5