【MySQL】UPDATE文の書き方:サンプル多数あり
ここでは、MySQLデータベースで、表・テーブルに存在するデータを更新・変更するSQLであるUPDATE文について紹介します。
UPDATE文の基本構文に加えて、多数のサンプルを掲載していますのでぜひ参考にしてください。
UPDATE文の基本構文
UPDATE文の基本的な構文は次の通りです。
WHERE句の条件は任意でつけることが出来ます。
条件を指定することで、更新対象の行データを選択できます。全く条件を指定しない場合は、表の全てのデータを一括更新します。
更新する列については、一度に複数の列を更新することも出来ます。
UPDATE テーブル名 SET 列名 = 値[,列名 = 値] [WHERE 条件];
UPDATE文のSQLサンプル
テーブル全件の一括更新
UPDATE文でWHERE句で条件を指定しない場合は、テーブルの全てのレコードを一括更新します。
UPDATE 表名 SET 列名 = 値;
次のSQLでは、emp_name列の値を全てtestという文字列に一括更新しています。
mysql> UPDATE tab1 SET emp_name = 'test';
Query OK, 6 rows affected (0.10 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> SELECT emp_name FROM tab1;
+----------+
| emp_name |
+----------+
| test |
| test |
| test |
| test |
| test |
| test |
+----------+
6 rows in set (0.01 sec)
テーブルの部分更新
UPDATE文にWHERE句で条件を指定することで、更新対象を絞って部分更新することが出来ます。
UPDATE 表名 SET 列名 = 値 WHERE 条件;
次の例では、「WHERE emp_id = ‘00001’」として、emp_idが’00001’のレコードだけを更新しています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.02 sec)
mysql> UPDATE tab1 SET emp_name = 'test' WHERE emp_id = '00001';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | test | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
複数レコードの更新
WHERE句の条件の指定で複数のレコードを同時に更新することが出来ます。
UPDATE 表名 SET 列名 = 値 WHERE 条件1 OR 条件2...;
次の例では「WHERE emp_id = ‘00001’ OR emp_id = ‘00002’」とすることでemp_idが00001もしくは00002のレコードを更新しています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> UPDATE tab1 SET emp_name = 'test'
-> WHERE emp_id = '00001' OR emp_id = '00002';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | test | D0001 | 28 |
| 00002 | test | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
IN句を指定した更新
WHERE句の条件にIN句を使うことで、指定した値のリストに一致するレコードを対象に更新を行うことが出来ます。
条件をOR句で羅列することもできますが、IN句を使うことでよりスマートに記述することができます。
UPDATE 表名 SET 列名 = 値 WHERE 列名 IN (値1,値2...);
次の例では「emp_id IN (‘00001′,’00002′,’00003′)」とするこで、emp_id列の値が’00001′,’00002’,’00003’という値のいずれかに一致するレコードを更新対象にしています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> UPDATE tab1 SET emp_name = 'test'
-> WHERE emp_id IN ('00001','00002','00003');
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | test | D0001 | 28 |
| 00002 | test | D0002 | 25 |
| 00003 | test | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
LIKE演算子を指定した更新
LIKE演算子を使うことで、「任意の文字を含む」や「任意の文字で始まる/終わる」レコードを対象として更新することが出来ます。
--前方一致の例
UPDATE 表名 SET 列名 = 値 WHERE 列名 LIKE '値%';
--中間一致の例
UPDATE 表名 SET 列名 = 値 WHERE 列名 LIKE '%値%';
--後方一致の例
UPDATE 表名 SET 列名 = 値 WHERE 列名 LIKE '%値';
次の例ではemp_name列が”za”という文字列を含むレコードを対象にUPDATEしています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> UPDATE tab1 SET emp_name = 'test'
-> WHERE emp_name LIKE '%za%';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | test | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | test | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
複数カラムの更新
UPDATE文では、SET句に複数の列を指定することで、同時に複数カラムの値を更新することが出来ます。
UPDATE 表名 SET 列名1 = 値1,列名2 = 値2,...;
次の例では、emp_name列とage列を一回のUPDATE文で同時に更新しています。ここでは全件更新していますが、WHERE句の条件を組み合わせることも可能です。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> UPDATE tab1 SET emp_name='test',age=100;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | test | D0001 | 100 |
| 00002 | test | D0002 | 100 |
| 00003 | test | D0002 | 100 |
| 00004 | test | D0003 | 100 |
| 00005 | test | D0003 | 100 |
| 00006 | test | D0003 | 100 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
複数テーブルの更新
UPDATE文では複数テーブルを同時に更新することが出来ます。
次の例では、tab1とtab2テーブルを結合し、同時に更新しています。合計で9行のレコードを同時に更新しています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.01 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
mysql> UPDATE tab1 a,tab2 b
-> SET a.emp_name='test1',b.dept_name='test2'
-> WHERE a.dept = b.dept;
Query OK, 9 rows affected (0.01 sec)
Rows matched: 9 Changed: 9 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | test1 | D0001 | 28 |
| 00002 | test1 | D0002 | 25 |
| 00003 | test1 | D0002 | 35 |
| 00004 | test1 | D0003 | 45 |
| 00005 | test1 | D0003 | 55 |
| 00006 | test1 | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | test2 |
| D0002 | test2 |
| D0003 | test2 |
+-------+-----------+
3 rows in set (0.00 sec)
複数のUPDATE文を1行で実行
UPDATE文を;(セミコロン)で区切って実行することで複数のUPDATE文を1行で実行することが出来ます。
UPDATE 表名 SET 列名1 = 値1;UPDATE 表名 SET 列名1 = 値1;
次の例では「UPDATE tab1 SET emp_name=’test’;UPDATE tab2 SET dept_name=’test’;」とすることでtab1とtab2テーブルを1行で更新しています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
mysql> UPDATE tab1 SET emp_name='test';UPDATE tab2 SET dept_name='test';
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6 Changed: 6 Warnings: 0
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | test | D0001 | 28 |
| 00002 | test | D0002 | 25 |
| 00003 | test | D0002 | 35 |
| 00004 | test | D0003 | 45 |
| 00005 | test | D0003 | 55 |
| 00006 | test | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | test |
| D0002 | test |
| D0003 | test |
+-------+-----------+
3 rows in set (0.00 sec)
別テーブルの値で更新
UPDATE文では、SET句で更新値に別テーブルの値を指定することで、別テーブルの値を使って更新することが出来ます。
次の例では、tab1テーブルのemp_name列を、別テーブルであるtab2のdept_name列の値を使って更新しています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
mysql> UPDATE tab1 a,tab2 b SET a.emp_name = b.dept_name
-> WHERE a.dept = b.dept;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | HR | D0001 | 28 |
| 00002 | SALES | D0002 | 25 |
| 00003 | SALES | D0002 | 35 |
| 00004 | DEV | D0003 | 45 |
| 00005 | DEV | D0003 | 55 |
| 00006 | DEV | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
JOINを使った更新
UPDATE文では、JOINした結果を使ってテーブルを更新することもできます。
次の例では、tab1とtab2をINNER JOINした結果と使ってtab1テーブルを更新しています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
mysql> UPDATE tab1 a INNER JOIN tab2 b ON a.dept = b.dept
-> SET a.emp_name = 'test'
-> WHERE b.dept_name = 'SALES';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | test | D0002 | 25 |
| 00003 | test | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
SELECT結果を使って更新
UPDATE文ではSELECT結果でテーブルを更新することが出来ます。
次の例ではtab1テーブルをtab2テーブルをSELECTした結果をサブクエリとして使って更新しています。
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.01 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
mysql> UPDATE tab1 a
-> SET emp_name = (SELECT dept_name FROM tab2 b
-> WHERE b.dept = 'D0002')
-> WHERE
-> a.dept = 'D0002';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | SALES | D0002 | 25 |
| 00003 | SALES | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
2つの表のSELECT結果が一致するレコードの更新
UPDATE文では、2つの表のSELECT結果が一致するレコードを更新することが出来ます。
次の例ではtab1とtab2のSELECTした結果が一致するレコードを更新しています。
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
| D0003 | DEV |
+-------+-----------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | Suzuki | D0001 | 28 |
| 00002 | Tanaka | D0002 | 25 |
| 00003 | Kizaki | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM tab2;
+-------+-----------+
| dept | dept_name |
+-------+-----------+
| D0001 | HR |
| D0002 | SALES |
+-------+-----------+
2 rows in set (0.00 sec)
mysql> UPDATE tab1 a,tab2 b
-> SET a.emp_name = 'test'
-> WHERE a.dept = b.dept;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM tab1;
+--------+----------+-------+------+
| emp_id | emp_name | dept | age |
+--------+----------+-------+------+
| 00001 | test | D0001 | 28 |
| 00002 | test | D0002 | 25 |
| 00003 | test | D0002 | 35 |
| 00004 | Tanaka | D0003 | 45 |
| 00005 | Kizaki | D0003 | 55 |
| 00006 | Nakata | D0003 | 35 |
+--------+----------+-------+------+
6 rows in set (0.00 sec)