Tutorial: Delete data in an Amazon Keyspaces table

To delete data in your employees_tbl table, use the DELETE statement.

You can delete data from a row or from a partition. Be careful when deleting data, because deletions are irreversible.

Deleting one or all rows from a table does not delete the table. Thus you can repopulate it with data. Deleting a table deletes the table and all data in it. To use the table again, you must re-create it and add data to it. Deleting a keyspace deletes the keyspace and all tables within it. To use the keyspace and tables, you must re-create them, and then populate them with data.

Deleting cells

Deleting a column from a row removes the data from the specified cell. When you display that column using a SELECT statement, the data is displayed as null, though a null value is not stored in that location.

The general syntax to delete one or more specific columns is as follows.

DELETE column_name1[, column_name2...] FROM table_name WHERE condition ;

In your employees_tbl table, you can see that the CEO has "None" for a manager. First, delete that cell so that you're not carrying any data in it.

To delete a specific cell

1. Run the following DELETE query.

   DELETE manager_id FROM "myGSGKeyspace".employees_tbl WHERE id='789-01-2345' AND division='Executive';

2. Verify that the delete was made as expected.

   SELECT * FROM "myGSGKeyspace".employees_tbl WHERE id='789-01-2345' AND division='Executive';

Deleting rows

There might be a time when you need to delete an entire row, such as when an employee retires. The general syntax for deleting a row is as follows.

DELETE FROM table_name WHERE condition ;

To delete a row

1. Run the following DELETE query.

   DELETE FROM "myGSGKeyspace".employees_tbl WHERE id='456-78-9012' AND division='Engineering';

2. Verify that the delete was made as expected.

   SELECT * FROM "myGSGKeyspace".employees_tbl WHERE id='456-78-9012' AND division='Engineering';