DELETE FROM or UPDATE

[nagydavid]

Hi All, I would like to have another question.

How can I delete records from a table.

DELETE FROM table_name WHERE condition;

I cannot find any reference to it.

only that it is implemented according to this issue.

[lostmygithubaccount]

hi @nagydavid, I don't believe there's any specific functionality in Ibis for deleting specific rows in a table. you can use the .sql() functionality to run arbitrary SQL, let us know if that doesn't work for your purposes. you would do something like:

import ibis

con = ibis.connect("duckdb://my_db.ddb")

# this might not work
con.sql("DELETE FROM table_name WHERE condition;")
# this should definitely work
con.raw_sql("DELETE FROM table_name WHERE condition;")

the more Ibis-y way would be something like:

import ibis

con = ibis.connect("duckdb://my_db.ddb")

t = con.tables.my_table

condition = t.column_name == "something"
t = t.filter(condition)

# overwrite the old table
con.create_table("my_table", t, overwrite=True)

[lostmygithubaccount]

(after a discussion w/ core maintainers) this is likely not something we will support in Ibis -- we only support a few DDL statements mostly around creating/dropping tables/views. the Ibis-y way to do this would be to create a new table filtered down appropriately.

if you can share your use case here, it is something we're open to supporting, but generally against we we don't expect Ibis to be used for transactional workloads with a lot of inserts, deletes, upserts, etc.