Tutorial: Databases

This tutorial shows us how to delete a row of data in a database table

Deleting a Row

Let's say that when we enter a record, we accidentally duplicate the data so we now have multiple entries of the same row of data. Most likely, we will not want to keep it or else that may cause inaccuracies inside of the database. As we have been working with a lot of data entries so far, we may want to start cleaning up our table and learn to remove unwanted data entries.

For this tutorial, we will learn to delete rows in our database table. We will want to connect our database once again as well as create a Delete object. To do this, we would create a new object called Delete, that allows us to use helper actions to properly delete a selected row. Let's go ahead and name this Delete object "delete." 

use Libraries.Data.Database.Database
use Libraries.Data.Database.Insert
use Libraries.Data.Database.Delete

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

Delete delete

With our delete object, we will be using the actions: SetTable(text tableName), ToText(), and Filter(text filterExpression). We will also be using the Delete(Delete objectName) action using our Database object.

Deleting Actions
Action / Object Description Usage
Delete objectNameThe Insert query represents the DELETE FROM command of the SQL (Structured Query Language) language. This query allows for data manipulation by deleting rows from an existing table.Delete delete
deleteObject:SetTable(text tableName)This action sets the table of the query. Most queries run against a single table and this action is used to set which table the query will affect or search. This action also lets you rename the table so in filters a different name can be used.delete:SetTable("PokemonList"
deleteObject:Filter(text filterExpression)Some queries make use of the filter which is an expression checked against the values of the columns in the rows of a database table. The filter is written using Quorum syntax and will be type checked to see if it's valid. Column names can be used and the names made by the user using name remaps. An example filter would be "priority < 4" where priority is a column that exists and can be mapped to an integer.delete:SetTable("PokemonList", "hp < 90")
deleteObject:ToText()By default convert to structured query language.delete:ToText()
databaseObject:Delete(Delete objectName)This takes an Delete query and sends it to the database and returns the result. A Delete removes rows from a database table based on a given filter and then returns the number of rows deleted. A delete at minimum needs a table and a filter so there are helper functions in this Database class to make it easier to run a delete but using an object is also accepted. For more information on the deleting there is documentation on the Delete query.database:Delete(delete)

What we will be wanting to do first is set up our Delete object and make sure that we have the right table selected and filtered to properly delete a row. We will once again be using our "grocerylist" table as we have multiple entries to work with. Let's go ahead and call the action SetTable() and pass in "grocerylist" inside the parameter; this will make sure the correct table is selected inside our object. Next, we will call in the Filter action and pass in an expression; in our case, we will not be buying the first item anymore so let's go ahead and set up our filter expression to be "FoodID = 1" so now, the Delete object will hold only that row in its container. We can also go ahead and output the translated SQL statement in Quorum Studio with the action ToText(). 

delete:SetTable("grocerylist") 
  delete:Filter("FoodID = 1")
  output delete:ToText()

Now that we have selected the row we wish to delete, we can go ahead and call the Delete(Delete objectName) action using our database object. Let's go ahead and pass in "delete" inside the parameter, and let's also save the return value inside a variable named rowsAffected. We want to confirm that it deleted one row. Finally, let's go ahead and output the variable to see how many rows have been modified onto our table (which again, should only be one row). Using this action, we can delete multiple rows at the same time using the Filter action.

integer rowsAffected = database:Delete(delete)
output "Deleted " + rowsAffected + " row(s)"

Congrats! We now know how to delete a row inside our database! We can view the entire code below.

use Libraries.Data.Database.Database
use Libraries.Data.Database.Insert
 use Libraries.Data.Database.Delete

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

  Delete delete
  delete:SetTable("grocerylist") 
  delete:Filter("FoodID = 1")
  integer rowsAffected = database:Delete(delete)
  output delete:ToText()

  output "Deleted " + rowsAffected + " row(s)"

Next Tutorial

In the next tutorial, we will discuss EmptyData, which describes how to empty a database table.

Go BackNext Tutorial