delete

(1) Delete records from a table

 delete from table_name
    [where condition(s)];

If where conditions are not used, the SQL statement deletes all records from the table.

(2) Delete records from a table, based on conditions involving another table.

delete table_name 
  from table_joiner(table_names)
  [where condition(s)];

where

• table_joiner can only be ej or lj.

• table_name must be the left table.

If where conditions are not used, the SQL statement deletes all matched records from the left table.

Note:

• For nested joins, only the outermost join can involve DFS tables.

• When joining an in-memory table (left) with a DFS table (right), the right table is loaded into memory.

• If chunkGranularity of a database is set to "CHUNK", a partitioned table of the database can only be the left table for joining.

• Joining tables from two different databases is not supported.

In MySQL dialect mode, the DELETE statement supports the following multi-table deletion syntax:

JOIN Syntax

DELETE table_alias FROM left_table [AS] table_alias 
  {INNER | LEFT} JOIN right_table [AS] alias2 ON join_condition
  [WHERE where_condition];

Comma-separated CROSS JOIN

DELETE alias1 FROM table1 [AS] alias1, table2 [AS] alias2
  [WHERE where_condition];

Notes

• The left table can be either a DFS table or an in-memory table.

• The target to be deleted (the alias specified after DELETE) must be the left table. Deleting multiple tables simultaneously (e.g., DELETE a, b FROM ...) is not supported and will return an error.

• Currently, only INNER JOIN, LEFT JOIN, and comma-separated CROSS JOIN are supported. RIGHT JOIN and the CROSS JOIN keyword are not supported.

• Only two-table joins are supported for deletion. Continuous joins involving three or more tables are not yet supported.

• In MySQL mode, line breaks, spaces, and comments are allowed between the table alias (after DELETE) and the FROM keyword.

t = table(1 1 1 2 2 2 3 3 3 3 as id, 1..10 as x);
t;

delete from t where id=1;
t;

delete from t where id=3, x>8;
t;

delete from t;
t;

Example 2. Delete the data in a dfs table

login(`admin, `123456)
n=1000000
ID=rand(10, n)
x=rand(1.0, n)
t=table(ID, x)
db=database("dfs://rangedb124", RANGE,  0 5 10)
pt=db.createPartitionedTable(t, `pt, `ID)$ pt.append!(t)
select count(*) from pt;1000000
delete from pt where ID=5;
select count(*) from pt;

Example 3. To delete data in distributed table, and the result of the function in where clause is sensitive to the order of the rows, the map clause must be used which mean the operation will be executed separately in the partition.

n=10000$ ID=take( 0..4, n)
date=take(2017.08.07..2017.11.11, n)
ts=rand(timestamp(1..n),n)
int=take(1..50,n)
str=rand(string(1..n),n)
sym=rand(symbol(string(1..n)),n)
x=rand(10.0, n)
t=table(ID, date,ts,int,str,sym, x)
if(existsDatabase("dfs://compoDB")){
dropDatabase("dfs://compoDB")
}
db = database("dfs://compoDB", VALUE,2017.08M..2017.10M)
pt = db.createPartitionedTable(t, `pt, `date)$ pt.append!(t);
delete from pt where isDuplicated([ID,int])=false map;

Example 4. Using delete with join

t1 = table(1..5 as id, [1,2,2,1,1] as flag)

t2 = table(3..7 as id, [100,200,100,150,100] as profit)

Delete records from t2 that have a matching id in both t1 and t2, where the flag in t1 is equal to 1.

delete t2 from ej(t2, t1, `id) where flag=1 
t2

Example 5. Multi-Table Delete in MySQL Mode (Currently supports two-table joins only)

By joining the performance table (perf) with the result table (trm), the statement identifies all contract records associated with a transaction sequence ID (tradeseq_id) of 3102 and removes them from the perf table. The code needs to run in MySQL mode (ensure the SQL mode is set to "MySQL" in the client).

create database "dfs://mosm"
partitioned by VALUE(2026.01M..2026.12M),
engine="OLAP",
atomic="CHUNK"

create table "dfs://mosm"."trm_result_contract_performance"(
    contract_id STRING,
    energy DOUBLE,
    price DOUBLE,
    create_time DATETIME
)
create table "dfs://mosm"."trm_result"(
    guid STRING,
    tradeseq_id INT,
    energy DOUBLE,
    price DOUBLE,
    update_time DATETIME
)

perf = loadTable("dfs://mosm", "trm_result_contract_performance")
trm = loadTable("dfs://mosm", "trm_result")

perf.append!(table(
    `G001`G002`G003`G004`G999 as contract_id,
    10 20 30 40 50 as energy,
    100 200 300 400 500 as price,
    2026.01.01T10:00:00 + 0..4 as create_time
))

trm.append!(table(
    `G001`G002`G003`G004 as guid,
    3101 3102 3102 3103 as tradeseq_id,
    1000 2000 3000 4000 as energy,
    1.1 2.2 3.3 4.4 as price,
    2026.01.01T09:00:00 + 0..3 as update_time
))

DELETE a FROM perf a left join trm b on a.contract_id = b.guid 
where b.tradeseq_id = 3102

select * from perf