Deleting is only supported for:
You can delete existing rows (documents) from an existing table based on ID or conditions.
Deleting documents is supported via SQL and HTTP interfaces.
SQL response for successful operation will show the number of rows deleted.
json/delete
is an HTTP endpoint for deleting. The server will respond with a JSON object stating if the operation was successful or not and the number of rows deleted.
To delete all documents from a table it's recommended to use instead the table truncation as it's a much faster operation.
- SQL
- JSON
DELETE FROM table WHERE where_condition
table
is a name of the table from which the row should be deleted.where_condition
for SQL has the same syntax as in the SELECT statement.
In this example we are deleting all documents that match full-text query dummy
from table named test
:
- SQL
- JSON
- PHP
- Python
- javascript
- Java
SELECT * FROM TEST;
DELETE FROM TEST WHERE MATCH ('dummy');
SELECT * FROM TEST;
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 100 | 1000 | 100,201 | 100 |
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 103 | 1003 | 103,204 | 103 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
| 106 | 1006 | 106,207 | 106 |
| 107 | 1007 | 107,208 | 107 |
+------+------+-------------+------+
8 rows in set (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 100 | 1000 | 100,201 | 100 |
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 103 | 1003 | 103,204 | 103 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
+------+------+-------------+------+
6 rows in set (0.00 sec)
Here - deleting a document with id
100 from table named test
:
- SQL
- JSON
- PHP
- Python
- javascript
- Java
DELETE FROM TEST WHERE id=100;
SELECT * FROM TEST;
Query OK, 1 rows affected (0.00 sec)
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 103 | 1003 | 103,204 | 103 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
+------+------+-------------+------+
5 rows in set (0.00 sec)
Manticore SQL allows to use complex conditions for the DELETE
statement.
For example here we are deleting documents that match full-text query dummy
and have attribute mva1
with a value greater than 206 or mva1
values 100 or 103 from table named test
:
- SQL
DELETE FROM TEST WHERE MATCH ('dummy') AND ( mva1>206 or mva1 in (100, 103) );
SELECT * FROM TEST;
Query OK, 4 rows affected (0.00 sec)
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
+------+------+-------------+------+
6 rows in set (0.00 sec)
Here is an example of deleting documents in cluster nodes4
's table test
:
- SQL
- JSON
- PHP
- Python
- javascript
- Java
delete from nodes4:test where id=100;
Array(
[_index] => test
[_id] => 100
[found] => true
[result] => deleted
)
Manticore supports basic transactions for deleting and inserting data into real-time and percolate tables. Each change to a table is first saved in an internal changeset and then actually committed to the table. By default, each command is wrapped in an individual automatic transaction, making it transparent: you simply 'insert' something and can see the inserted result after it completes, without worrying about transactions. However, this behavior can be explicitly managed by starting and committing transactions manually.
Transactions are supported for the following commands:
Transactions are not supported for:
- UPDATE (which is different from REPLACE, as it performs an in-place attribute update)
- ALTER - for updating table schema
- TRUNCATE - for emptying a real-time table
- ATTACH - for attaching a plain table to a real-time table
- CREATE - for creating a table
- DROP - for deleting a table
Please note that transactions in Manticore do not aim to provide isolation. The purpose of transactions in Manticore is to allow you to accumulate multiple writes and execute them all at once upon commit, or to roll them all back if necessary. Transactions are integrated with binary log for durability and consistency.
SET AUTOCOMMIT = {0 | 1}
SET AUTOCOMMIT
controls the autocommit mode in the active session. AUTOCOMMIT
is set to 1 by default. With the default setting, you don't have to worry about transactions, as every statement that makes any changes to any table is implicitly wrapped in a separate transaction. Setting it to 0 allows you to manage transactions manually, meaning they will not be visible until you explicitly commit them.
Transactions are limited to a single real-time or percolate table and are also limited in size. They are atomic, consistent, overly isolated, and durable. Overly isolated means that the changes are not only invisible to concurrent transactions but even to the current session itself.
START TRANSACTION | BEGIN
COMMIT
ROLLBACK
The BEGIN
statement (or its START TRANSACTION
alias) forcibly commits any pending transaction, if present, and starts a new one.
The COMMIT
statement commits the current transaction, making all its changes permanent.
The ROLLBACK
statement rolls back the current transaction, canceling all its changes.
When using the /bulk JSON endpoint, you can force a batch of documents to be committed by adding an empty line after them.
insert into indexrt (id, content, title, channel_id, published) values (1, 'aa', 'blabla', 1, 10);
Query OK, 1 rows affected (0.00 sec)
select * from indexrt where id=1;
+------+------------+-----------+--------+
| id | channel_id | published | title |
+------+------------+-----------+--------+
| 1 | 1 | 10 | blabla |
+------+------------+-----------+--------+
1 row in set (0.00 sec)
The inserted value is immediately visible in the following 'select' statement.
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (3, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (4, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
select * from indexrt where id=3;
Empty set (0.01 sec)
select * from indexrt where id=4;
Empty set (0.00 sec)
In this case, changes are NOT automatically committed. As a result, the insertions are not visible, even in the same session, since they have not been committed. Also, despite the absence of a BEGIN
statement, a transaction is implicitly started.
So, let's finally commit it:
commit;
Query OK, 0 rows affected (0.00 sec)
select * from indexrt where id=4;
+------+------------+-----------+-------+
| id | channel_id | published | title |
+------+------------+-----------+-------+
| 4 | 1 | 1 | bb |
+------+------------+-----------+-------+
1 row in set (0.00 sec)
select * from indexrt where id=3;
+------+------------+-----------+-------+
| id | channel_id | published | title |
+------+------------+-----------+-------+
| 3 | 1 | 1 | bb |
+------+------------+-----------+-------+
1 row in set (0.00 sec)
Now it is finished and visible.
By using BEGIN
and COMMIT
, you can define the bounds of a transaction explicitly, so there's no need to worry about autocommit in this case.
begin;
Query OK, 0 rows affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (2, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
select * from indexrt where id=2;
Empty set (0.01 sec)
commit;
Query OK, 0 rows affected (0.01 sec)
select * from indexrt where id=2;
+------+------------+-----------+-------+
| id | channel_id | published | title |
+------+------------+-----------+-------+
| 2 | 1 | 1 | bb |
+------+------------+-----------+-------+
1 row in set (0.01 sec)