SQLのViewについてまとめています。
MySQLのサンプルデータベースEmployeesを使用しています。
SQLのviewは、viewに対して更新が可能
シンプルなselect文で作成したviewはupdate文による更新が可能です。
以下は、employees（社員）テーブルのemp_no（社員番号）、first_name（名）、last_name（姓）のみを使ったviewに対して、first_nameを更新するサンプルです。
# viewを作成 mysql> create view updtest as select emp_no, first_name, last_name from employees; Query OK, 0 rows affected (0.03 sec) # viewの構造を表示 mysql> desc updtest; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | emp_no | int | NO | | NULL | | | first_name | varchar(14) | NO | | NULL | | | last_name | varchar(16) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) # viewから10件表示j mysql> select * from updtest limit 10; +--------+------------+-----------+ | emp_no | first_name | last_name | +--------+------------+-----------+ | 10001 | Georgi | Facello | | 10002 | Bezalel | Simmel | | 10003 | Parto | Bamford | | 10004 | Chirstian | Koblick | | 10005 | Kyoichi | Maliniak | | 10006 | Anneke | Preusig | | 10007 | Tzvetan | Zielinski | | 10008 | Saniya | Kalloufi | | 10009 | Sumant | Peac | | 10010 | Duangkaew | Piveteau | +--------+------------+-----------+ 10 rows in set (0.00 sec) # viewに対してfirst_nameカラムを更新 mysql> update updtest set first_name="xGeorgi" where emp_no="10001"; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 更新後のviewを確認 mysql> select * from updtest where emp_no="10001"; +--------+------------+-----------+ | emp_no | first_name | last_name | +--------+------------+-----------+ | 10001 | xGeorgi | Facello | +--------+------------+-----------+ 1 row in set (0.00 sec) # 更新後の元テーブルを確認 mysql> select * from employees where emp_no="10001"; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | xGeorgi | Facello | M | 1986-06-26 | +--------+------------+------------+-----------+--------+------------+ 1 row in set (0.00 sec)
【関連記事】
▶SQLのview作成 joinやサブクエリを使ったselectも使用可能
ただし、単一テーブルの特定のカラムをselectするようなシンプルなviewでのみupdateが可能です。例えば、以下の様な場合は、update実行時にエラーとなります。
joinを使ったviewに更新失敗
employees（社員）テーブル、dept_emp（部署名・社員紐付け）テーブル、departments（部署）テーブルの3テーブルをjoinするviewに対してupdateを実行すると、同様にエラーになりました。
create view jointest as SELECT employees.emp_no, first_name, last_name, dept_name FROM employees left join dept_emp on employees.emp_no = dept_emp.emp_no and first_name like 'G%' left join departments on dept_emp.dept_no = departments.dept_no Query OK, 0 rows affected (0.04 sec) mysql> desc jointest; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | emp_no | int | NO | | NULL | | | first_name | varchar(14) | NO | | NULL | | | last_name | varchar(16) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> select * from jointest limit 10; +--------+------------+-----------+-------------+ | emp_no | first_name | last_name | dept_name | +--------+------------+-----------+-------------+ | 10001 | Georgi | Facello | Development | | 10002 | Bezalel | Simmel | NULL | | 10003 | Parto | Bamford | NULL | | 10004 | Chirstian | Koblick | NULL | | 10005 | Kyoichi | Maliniak | NULL | | 10006 | Anneke | Preusig | NULL | | 10007 | Tzvetan | Zielinski | NULL | | 10008 | Saniya | Kalloufi | NULL | | 10009 | Sumant | Peac | NULL | | 10010 | Duangkaew | Piveteau | NULL | +--------+------------+-----------+-------------+ 10 rows in set (0.00 sec) mysql> update jointest set first_name="xGeorgi" where emp_no="10001"; ERROR 1288 (HY000): The target table jointest of the UPDATE is not updatable
こちらの場合は、The target table jointest of the UPDATE is not updatableと、対象テーブルがupdateできない旨のエラーが表示されました。
サブクエリを使ったｖｉｅｗでupdate失敗
create view subquerytest as select * from (select emp_no, first_name, last_name, hire_date from employees where birth_date > 1970-01-01 and gender = 'F' union all select emp_no, first_name, last_name, hire_date from employees where birth_date < 1960-01-01 and gender = 'M' ) t1 order by last_name, emp_no mysql> desc subquerytest; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | emp_no | int | NO | | 0 | | | first_name | varchar(14) | NO | | | | | last_name | varchar(16) | NO | | | | | hire_date | date | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> select * from subquerytest limit 10; +--------+------------+-----------+------------+ | emp_no | first_name | last_name | hire_date | +--------+------------+-----------+------------+ | 12516 | Sreenivas | Aamodt | 1990-03-06 | | 12982 | Sachem | Aamodt | 1992-01-11 | | 17400 | Basim | Aamodt | 1991-09-15 | | 18182 | Dekang | Aamodt | 1988-05-25 | | 27188 | Vasilii | Aamodt | 1996-10-12 | | 27413 | Phuoc | Aamodt | 1987-08-28 | | 28387 | Nahla | Aamodt | 1997-02-15 | | 29182 | Arumugam | Aamodt | 1986-01-09 | | 31701 | Carrsten | Aamodt | 1985-10-07 | | 35101 | Christ | Aamodt | 1990-02-05 | +--------+------------+-----------+------------+ 10 rows in set, 2 warnings (0.91 sec) mysql> update subquerytest set first_name="xSreenivas" where emp_no="12516"; ERROR 1292 (22007): Incorrect date value: '1968' for column 'birth_date' at row 1
社員番号12516に対してfirst_nameをupdateしようとしたところ、Incorrect date value: ‘1968’ for column ‘birth_date’エラーとなりました。
view作成時に指定したselect文が複雑になると、予想外のエラーが表示されるようです。
まとめ
ポテパンダの一言メモ
- viewに対するupdateは条件付きで可能
- サブクエリやjoinを使った複雑なviewは、update発行時にエラーとなる
- The target table jointest of the UPDATE is not updatable以外のエラーとなるケースもある
