In Machbase, this statement is only performed in log tables. In addition, the data located in a random position cannot be deleted, but it is allowed to delete data consecutively from a certain point to the last record (the oldest log). This was designed based on characteristics of log data so that once data is inserted, modification is not allowed. It is only allowed to delete data for securing enough storage space in the database.
DELETE FROM table_name; DELETE FROM table_name OLDEST number ROWS; DELETE FROM table_name EXCEPT number ROWS; DELETE FROM table_name EXCEPT number [YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND]; DELETE FROM table_name BEFORE datetime_expr;
mach>DELETE FROM devices; 10 row(s) deleted. mach>DELETE FROM devices OLDEST 5 ROWS; 10 row(s) deleted. mach>DELETE FROM devices EXCEPT 5 ROWS; 15 row(s) deleted. mach>DELETE FROM devices BEFORE TO_DATE('2014-06-01', 'YYYY-MM-DD'); 50 row(s) deleted.
DELETE WHERE syntax can only be operated in volatile and lookup tables. It can delete the records that match with the conditions in WHERE clause.
- It can be used for a volatile and lookup table where the primary key is specified.
- Only "(primary key column) = (value)" condition is allowed in WHERE clause and it cannot be used with other conditions.
- It can only be used for the primary key column.
DELETE FROM table_name WHERE primary_column = value;
mach> DELETE FROM where_table WHERE id=1; 1 row(s) deleted.