85
90

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

レコードがなければINSERT,あればUPDATEをするSQL

Last updated at Posted at 2013-06-03

PrimaryキーまたはUniqueキーを指定して、レコードがなければINSERT、あればUPDATEしたい。

#mysql

##INSERT … ON DUPLICATE KEY UPDATE文を使う

mysql> desc foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| code | varchar(32) | NO | PRI | | |
| name | varchar(256) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+

mysql> insert into foo (code, name ) values ('0001', 'abc') on duplicate key update code='0001', name='def';

code='0001'のレコードがないので、insert

mysql> select * from foo;
+------+------+
| code | name |
+------+------+
| 0001 | abc |
+------+------+

mysql> insert into foo (code, name ) values ('0001', 'abc') on duplicate key update code='0001', name='def';

code='0001'のレコードがあるので、name='def'でupdate
+------+------+
| code | name |
+------+------+
| 0001 | def |
+------+------+

#oracle

##MERGE文を使う(9i以降)

Ex.
merge into user_master a
using (select '0056ddgd456' as user_id, 700 as user_point from dual ) b
on (a.user_id = b.user_id)
when matched then
update set
user_point = user_point + 100
when not matched then
insert (user_id, user_point)
values ('0056ddgd456', 100 );

dual表を使わない書き方もあるみたい。

85
90
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
85
90

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?