Delete a Row by Index

Question

Given the following table:

t:([] sym:`AAPL`MSFT`GOOG`TSLA`NVDA;
      qty:100 200 150 75 300;
      price:185.25 410.10 142.80 238.50 875.20)

Result:

sym  qty price
-----------------
AAPL 100 185.25
MSFT 200 410.1
GOOG 150 142.8
TSLA 75  238.5
NVDA 300 875.2

Delete the second row (index 1) from the table.

Expected result:

sym  qty price
-----------------
AAPL 100 185.25
GOOG 150 142.8
TSLA 75  238.5
NVDA 300 875.2

Solution

Method 1

delete from t where i=1

Method 2

t _ 1

Result:

sym  qty price
---------------
AAPL 100 185.25
GOOG 150 142.8
TSLA 75  238.5
NVDA 300 875.2

Explanation

Tables have an implicit row index named i.

For the table t, the rows have indices (shown here as an extra column for illustration; i is not a real column of t):

i sym  qty price
-------------------
0 AAPL 100 185.25
1 MSFT 200 410.1
2 GOOG 150 142.8
3 TSLA 75  238.5
4 NVDA 300 875.2

Using qSQL

The delete statement can filter rows using the implicit index:

delete from t where i=1

Result:

sym  qty price
---------------
AAPL 100 185.25
GOOG 150 142.8
TSLA 75  238.5
NVDA 300 875.2

This approach is useful when the row to be removed is identified by a condition.

For example:

delete from t where qty<100

or:

delete from t where sym=`TSLA

Using Drop

The Drop operator can remove rows by position:

t _ 1

Result:

sym  qty price
---------------
AAPL 100 185.25
GOOG 150 142.8
TSLA 75  238.5
NVDA 300 875.2

Here the right argument, 1, is the index of the row to remove.

This syntax is concise when you already know the position of the row.

Summary

Two common approaches are:

delete from t where i=1

t _ 1

The qSQL solution is generally more flexible because it can easily be extended to remove rows based on arbitrary conditions, while _ is convenient when removing rows by index.