SQLのupdateについてまとめています。
目次
SQLのUPDATEの構文
updateは、指定したテーブルの値を更新するSQLです。以下は、employees2テーブルのemp_no=10001のレコードのfirst_nameをTarou、last_nameをYamadaに書き換える例です。update文を実行する前後でselect文を実行し、値が更新されたことを確認しています。
mysql> select * from employees2 limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec) mysql> update employees2 set first_name='Tarou', last_name='Yamada' where emp_no=10001; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employees2 limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Tarou | Yamada | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec)
【関連記事】
▶SQLでアップデートする方法｜様々な条件付きでデータを更新するやり方も解説
Oracleのupdateの構文
UPDATE [ hint ]
{ dml_table_expression_clause
| ONLY (dml_table_expression_clause)
} [ t_alias ]
update_set_clause
[ where_clause ]
[ returning_clause ]
[error_logging_clause] ;
Oracleでは、RETURNING句を使って、更新された行の値をPL/SQL変数に格納することが可能です。以下のSQLでは、更新された行のsalary*0.25、last_name、department_idをそれぞれPL/SQL変数のbnd1、bnd2、bnd3に格納します。
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
また、Oracleではselect文にfor updateを指定して行単位のロックをかけることができます。
【関連記事】
▶SQLのfor updateは行レベルでテーブルをロックする トランザクションで有効
SQL Serverのupdateの構文
[ WITH <common_table_expression> [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ { table_alias | <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
| @table_variable
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
[ <OUTPUT Clause> ]
[ FROM{ <table_source> } [ ,...n ] ]
[ WHERE { <search_condition>
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
[ OPTION ( <query_hint> [ ,...n ] ) ]
[ ; ]
参考）UPDATE (Transact-SQL) – SQL Server | Microsoft Docs
SQL Serverでは、TOP句を使用して更新される行の数を指定することが可能です。指定条件が複数の行に合致する場合に、指定した件数のみ更新できるんですね。
ただし、更新される対象はランダムになります。以下のSQLのケースでは、HumanResourcesデータベースのEmployeeテーブルに対して、ランダムに10件、VacationHoursの値を25%増し（1.25倍）に更新します。
USE AdventureWorks2012; GO UPDATE TOP (10) HumanResources.Employee SET VacationHours = VacationHours * 1.25 ; GO
MySQLのupdateの構文
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.11 UPDATE 構文
MySQLでは、複数テーブルの更新が可能です。以下のSQLでは、itemsテーブルとmonthテーブルに対して、idが同一のデータの価格（price）を更新しています。
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
上記の記述方法以外にも、SELECT文で使用可能な結合（LEFT JOIN）などを使用可能です。
PostgreSQLのupdateの構文
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
PostgreSQLのupdateは標準SQLに準拠していますが、FROM句、RETURNING句、WITHはPostgreSQLの拡張です。
FROM句を使うと、他テーブルの結合結果を更新対象にすることができるんですね。下記SQLのケースでは、顧客名Acme Corporationを持つ営業担当のsales_countを1増加します。
UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE accounts.name = 'Acme Corporation' AND employees.id = accounts.sales_person;
標準SQLで記述すると、以下のように副問合せを使ったSQLと同じ操作になります。
UPDATE employees SET sales_count = sales_count + 1 WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
SQLのupdateのまとめ
- updateは、データの更新を行なうSQL
- updateは標準SQLだが、TOP句、FROM句やRETURNING句など主要DBMSごとに独自拡張あり
- 一部のDBMSでは、select文にfor updateを指定して、更新用に行単位のロックが可能
