Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Data

...

Insert

The data entry insert of the volatile table is as follows.

Code Block
languagesql
Mach> create volatile table vtable (id integer, name varchar(20));
Created successfully.
Mach> insert into vtable values(1, 'west device');
1 row(s) inserted.
Mach> insert into vtable values(2, 'east device');
1 row(s) inserted.
Mach> insert into vtable values(3, 'north device');
1 row(s) inserted.
Mach> insert into vtable values(4, 'south device');
1 row(s) inserted.

Data Update

When inputting data in a volatile table, data with duplicate primary key values ​​can be updated using the ON DUPLICATE KEY UPDATE clause.

Update Data Value to be Inserted

If the INSERT statement specifies data to be inserted, but there is other data that matches the primary key value of the insert data, the INSERT statement fails and the corresponding data is not inserted. If there is another data that matches the primary key value of the insertion data, and if you wish to update the corresponding data instead of insertion, a  ON DUPLICATE KEY UPDATE clause can be added.

...

Code Block
languagesql
Mach> create volatile table vtable (id integer primary key, direction varchar(10), refcnt integer);
Created successfully.
Mach> insert into vtable values(1, 'west', 0);
1 row(s) inserted.
Mach> insert into vtable values(2, 'east', 0);
1 row(s) inserted.
Mach> select * from vtable;
ID          DIRECTION   REFCNT      
----------------------------------------
1           west       0           
2           east        0           
[2] row(s) selected.

Mach> insert into vtable values(1, 'south', 0);
[ERR-01418 : The key already exists in the unique index.]
Mach> insert into vtable values(1, 'south', 0) on duplicate key update;
1 row(s) inserted.

Mach> select * from vtable;
ID          DIRECTION   REFCNT      
----------------------------------------
1           south        0           
2           east        0           
[2] row(s) selected.


Specify Data Value to be Updated

Similar to above, but if you need to update to a different column value than the data value to be inserted, it can be specified through the ON DUPLICATE KEY UPDATE SET clause. The data value to be updated can be specified under the SET clause.

...