【MySQL】レコードを、別テーブルの特定の値で UPDATE する方法

DB&サーバ

2013.07.24

この歳まで生きてきて「戻り梅雨」という概念をさっき初めて知った kimoto です。

データベースの情報をアップデートする際に、テーブルをまたがって行いたい時があります。例えば A というテーブルのデータを B というテーブルの特定のデータに置き換える、とか。 UPDATE 文をいくつも書いてひとつひとつやる方法もあるし、スクリプトを書いてループで回すやり方もあるでしょう。
今日は、自分へのメモも兼ねて SQL 文だけで行うやり方を書いてみます。

TableA の値を TableB の値で更新する

まず、TabelA の内容を TableB の内容にする方法です。

例えば、TableA の name というフィールドの値を、TableB のうち同じ id を持つレコードの name で上書きしたいとします。
ちなみに、id は一意とう前提です。その場合、流す UPDATE 文は単純。

UPDATE TableA, TableB 
SET TableA.name = TableB.name 
WHERE TableA.id = TableB.id;

これで、同じ id のレコードの name で更新されました。

TableA に属する TableB の値を、TableA が属する TableC の値で更新する

次に応用編。見出しはちょっとわかりづらい表現になっちゃいました。

例えば、Users というテーブルがあり、そこの1レコードである User が所有する情報 Infos というテーブルがあるとします。そして逆に、 User が属する Companies というテーブルがあるとします。
それぞれの紐付けは Infos.user_id と Users.id 、Users.company_id と Companies.id という感じでつながってるとします。

この時に、Infos の name というフィールドを、所有者が所属している Companies の name で上書きしたい場合を考えます。

この場合、Infos と Companies の間に直接的な接点がないため、先ほどのような単純な文ではいけません。
しかし、先ほどの TableB の部分に SELECT 文を使って Infos.id と Companies.name の組み合わせ持ってくることで実現できます。

まずは準備としてデータ取得の SQL 文を考えてみます。

SELECT Infos.id AS id, Companies.name AS name
FROM Infos 
    LEFT JOIN Users ON Infos.user_id = Users.id
    LEFT JOIN Companies ON Users.company_id = Companies.id;

Infos、Users、Companies を JOIN でつなげて、Infos.id と Companies.name の組み合わせでデータを持ってきました。
では、これを先ほどの UPDATE 文に入れ込んでいきましょう。

UPDATE Infos,  (SELECT Infos.id AS id, Companies.name AS name FROM Infos LEFT JOIN Users ON Infos.user_id = Users.id LEFT JOIN Companies ON Users.company_id = Companies.id) AS Com
SET Infos.name = Com.name 
WHERE Infos.id = Com.id;

これで目的の上書きができました。

この、複数テーブルにまたがって UPDATE を行うやり方をついつい忘れがちなので、自分用も兼ねて書いてみました。
参考になれば幸いです。