SingleStore supports multiple ways to delete data from a table using another table.

SingleStore: Delete From Table

There are many scenarios where it is required to delete data either directly from a table or using another table.

➠ Sample Table and Data

Creating Tables: Creating 2 tables with similar structure to understand the logic in details.

Table 1
CREATE ROWSTORE TEMPORARY TABLE deletetable(
   id INTEGER,
   name VARCHAR(100),
    SHARD KEY(id)
);

Table 2
CREATE ROWSTORE TEMPORARY TABLE othertable(
   id INTEGER,
   name VARCHAR(100),
    SHARD KEY(id)
);


Populating Tables: Inserting relevant data in newly created tables for understanding logic.

Table 1:
INSERT INTO deletetable VALUES(1,'SingleStore');
INSERT INTO deletetable VALUES(2,'Database');
INSERT INTO deletetable VALUES(3,'Oracle');
INSERT INTO deletetable VALUES(4,'Vertica');

Table 2:
INSERT INTO othertable VALUES(1,'SingleStore');
INSERT INTO othertable VALUES(2,'Database');
INSERT INTO othertable VALUES(5,'DB2');
INSERT INTO othertable VALUES(6,'MYSQL');

➠ Basic Delete : Deleting from single table

DELETE FROM deletetable 
WHERE id=4;

Output:
Query OK, 1 row affected

➠ Different approaches can be used to delete data from one table using other table.

Approach 1 : Delete with the help of other table using EXISTS condition.

DELETE FROM deletetable
WHERE EXISTS (SELECT 1 FROM othertable WHERE deletetable.id = othertable.id);

Output:
Query OK, 2 rows affected


Approach 2 : Delete with the help of other table using IN condition. This approach should be avoided.

DELETE FROM deletetable
WHERE id in (SELECT id FROM othertable);

 Output:
Query OK, 2 rows affected


Note: DELETE using traditional WHERE clause join is not supported, if users will try to do DELETE this way then it will fail with below error.

ERROR 1149 (42000): Target of Multi-Table Delete ambiguous