Tutorial: Databases

This tutorial shows us how to update a table from our database

Updating Our Table

Over time, data becomes outdated and database managers or architects are required to constantly update data so users or other administrators do not use outdated information. An example data changes in the real world are Disneyland tickets; overtime, Disneyland increases their prices from $45 in 2000 to $99 in 2015 to $159 in 2022, and as a database architect, it is necessary to update prices inside a table so consumers know how much to pay when ordering a ticket on a website (although, it would not be a terrible idea to keep it at the price during 2000).

We will want to connect our database once again as well as create an Update object. To do this, we would create a new object called Update, that allows us to use helper actions to delete the existing table inside of our database.

use Libraries.Data.Database.Database
use Libraries.Data.Database.Update

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

Update query

With our Update object, we are now able to filter and modify existing data within the table. Because we have multiple data entries on our table, we'll want to confirm that our filter worked which we can test using UsingFilter() because it is important that we modify one record of our data instead of the entire column in our table. To have access to these Filter and Add actions, we will be creating our Insert object and call it 'query.' The actions we will be using are Add(text column, dataType), Filter(text expression), and UsingFilter(), Update(Update objectName), and SetTable(text tableName).

Updating Database Table Actions
Action / Object Description Usage
Update objectNameThe Update query represents the Update command of the SQL (Structured Query Language) language. This query allows for the modification of a record that already exists in the database.Update query
updateObject: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.query:SetTable("Theme Park Prices")
updateObject:Filter(text expression)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.query:Filter("price > 120")
updateObject:UsingFilter()This action returns whether or not this Update query will use a filter. Returns boolean: Returns true if not using a filter and false if it willquery:UsingFilter()
updateObject:Add(text columnName, dataType modifedEntry)This action is used to add which column will be modified and what value it will be set to. query:Add("price", 140)
databaseObject:Update(Update objectName)This takes an Update query and sends it to the database and returns the result. An Update allows for changing the data in rows that already exist on the database. database:Update(query)

What we want to do first is use SetTable() on our 'query' object which allows us to select a specific table inside our database. For example, SetTable() takes in one parameter, the table title and in this case we will be passing in "grocerylist" into the action.

Next, what we will be wanting to do is filter our table so that we will only be targeting one row. The row(s) we'll be wanting to change are when foodCost > 3.00, and we can see from our previous insert that "Italian Sausage" is the only row that will be affected. We will be using our 'query' object and call Filter() and pass in "foodCost > 3.00" inside the parameter. Let's confirm this filter worked by calling the action UsingFilter() on 'query' and output that value; if true, the filter was successful.

Next, what we will be wanting to do is modify values using Add(); the action add requires two parameters: the column name and the data type value associated with the column. For example, say that we have a column called "firstName," we would want to pass "firstName" in the first parameter and because names are strings, we will insert "Bob" as the second parameter. In our grocerylist table, let's update foodCost from 3.99 to 3.50 and specify our Italian Sausage to "Italian Sausage (Hot)."

query:SetTable("grocery_list")
query:Filter("foodCost > 3.00")
boolean filtering = query:UsingFilter()
output "If it is using filter correctly value should be true: " +  filtering
query:Add("foodCost", 3.50)
query:Add("foodName", "Italian Sausage (Hot)")

Our data has successfully changed, however it is not actually reflected on the table yet. To do this, we will be wanting to call the Update() action from the database library and pass in our Update object to it which will reflect it onto our database. We can also call ToText() onto 'query' to confirm that there has been an update on our selected row.

database:Update(query)

Congrats! We have modified a row inside our data table. We can view the entire code below.


use Libraries.Data.Database.Database
use Libraries.Data.Database.Update

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

database:SetAutoCommit(false)
output "Status of Auto-Commit:" + database:GetAutoCommit()

 Update update
 update:SetTable("grocerylist")
 update:Filter("FoodQuantity = 7")
 update:Add("FoodQuantity", 1)

integer rows = database:Update(update)

    if rows > 1
        database:Rollback()
    else
        database:Commit()
    end

database:SetAutoCommit(true)
output "Status of Auto-Commit:" + database:GetAutoCommit()

Next Tutorial

In the next tutorial, we will discuss commiting data, which describes how to manually commit data onto a database.

Go BackNext Tutorial