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).
|Action / Object||Description||Usage|
|Update objectName||The 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|
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.
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()
In the next tutorial, we will discuss commiting data, which describes how to manually commit data onto a database.