MySQLでselect文の結果を使ってupdateする方法についてまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。
単純な値参照は、update文のみで実行可能
departments（部署）テーブルをコピーしたdepartments2テーブルに対して、departmentsテーブルの値を参照してupdateを実行するサンプルです。
departments2テーブルは、d001←no name、d002←no name2、d003←no name3と値を書き換えてあります。
mysql> select * from departments2 order by dept_no; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d001 | no name | | d002 | no name2 | | d003 | no name3 | | d004 | Production | | d005 | Development | | d006 | Quality Management | | d007 | Sales | | d008 | Research | | d009 | Customer Service | +---------+--------------------+ 9 rows in set (0.00 sec)
以下のSQLを実行します。
mysql> update departments, departments2 set departments2.dept_name= departments.dept_name where departments2.dept_no=departments.dept_no;
departments2テーブルのdept_nameを、同じdept_noのdepartmentsテーブルのdept_nameで更新するというSQLです。
実行するとこうなります。
mysql> select * from departments2 order by dept_no; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d001 | Marketing | | d002 | Finance | | d003 | Human Resources | | d004 | Production | | d005 | Development | | d006 | Quality Management | | d007 | Sales | | d008 | Research | | d009 | Customer Service | +---------+--------------------+ 9 rows in set (0.00 sec)
departmentsテーブルは参照にのみ使っていて何も更新されません。update文には、複数のテーブルを指定することができ、単純な参照であればupdate文だけでselect＆updateのような処理が実行できるんですね。
サブクエリでselectした値を使ってupdate
複雑なselect文の結果をupdateの値として使うにはサブクエリを使います。
mysql> update departments2 set dept_name=(select first_name from employees where emp_no="10001") where dept_no="d001";
上記SQLを実行すると、departments2のdept_noがd001のレコードに、employees（雇用者テーブル）のemp_no（社員番号）が10001のレコードのfirst_name（姓名の名）を挿入します。
実行すると、こうなります。
mysql> select * from departments2 order by dept_no; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d001 | Georgi | | d002 | Finance | | d003 | Human Resources | | d004 | Production | | d005 | Development | | d006 | Quality Management | | d007 | Sales | | d008 | Research | | d009 | Customer Service | +---------+--------------------+
d001のdept_nameが「Georgi」で更新されました。
なお、以下のように記述しても同じ結果になります。
mysql> update departments2, (select first_name from employees where emp_no="10001") tbl2 set dept_name=tbl2.first_name where dept_no="d001";
同じテーブルの値をselectしてupdateする
更新対象のテーブル自体の値を使った更新も可能です。
mysql> update departments2, (select * from departments2) tbl2 set departments2.dept_name=concat(tbl2.dept_name,'1') where departments2.dept_no=tbl2.dept_no;
上記SQLは、departments2のdept_nameの後ろに「１」をつけて更新します。
実行するとこうなります。
mysql> select * from departments2 order by dept_no; +---------+---------------------+ | dept_no | dept_name | +---------+---------------------+ | d001 | Georgi1 | | d002 | Finance1 | | d003 | Human Resources1 | | d004 | Production1 | | d005 | Development1 | | d006 | Quality Management1 | | d007 | Sales1 | | d008 | Research1 | | d009 | Customer Service1 | +---------+---------------------+
まとめ
- 単純な値参照はupdate文に参照先テーブル名を合わせて記述すれば可能
- select文の結果をupdate文で使うには、サブクエリを利用する
- 同じテーブルの値をselectしてupdateすることも可能
