sqlupdate - Update rows in SQLite database table - MATLAB (original) (raw)
Update rows in SQLite database table
Since R2023a
Syntax
Description
sqlupdate([conn](#mw%5F441270bc-16d4-4c48-9d45-fafa457d20e2%5Fsep%5Fshared-conn),[tablename](#mw%5F441270bc-16d4-4c48-9d45-fafa457d20e2%5Fsep%5Fshared-tablename),[data](#mw%5Fb49157d5-119b-4c5d-8859-10752284062f),[filter](#mw%5F4c182edb-8ffc-42e3-b358-2982a225b823)) updates rows in the SQLite database table (tablename) with the rows from the MATLAB® table (data) based on filter conditions (filter).
sqlupdate(___,[Name,Value](#namevaluepairarguments)) specifies additional options using one or more name-value arguments with any of the previous input argument combinations. For example, Catalog = "cat" updates data from a database table stored in the "cat" catalog.
Examples
Update Database Rows
Update rows in the database table in the SQLite database file based on filter conditions specified with row filters.
Create the SQLite connection to the existing SQLite database file inventory.db. The database file contains the table productTable. The SQLite connection is an sqlite object.
dbfile = "inventory.db"; conn = sqlite(dbfile);
Import all the data from productTable. The results output argument contains the imported data as a table. Display the first 10 rows of the table.
tablename = "productTable"; results = sqlread(conn,tablename); head(results,10)
productNumber stockNumber supplierNumber unitCost productDescription
_____________ ___________ ______________ ________ __________________
9 125970 1003 13 "Victorian Doll"
8 212569 1001 5 "Train Set"
7 389123 1007 16 "Engine Kit"
2 400314 1002 9 "Painting Set"
4 400339 1008 21 "Space Cruiser"
1 400345 1001 14 "Building Blocks"
5 400455 1005 3 "Tin Soldier"
6 400876 1004 8 "Sail Boat"
3 400999 1009 17 "Slinky"
10 888652 1006 24 "Teddy Bear" Use the SQL ALTER statement to add the column Recall to the table.
sqlquery = strcat("ALTER TABLE productTable ADD COLUMN Recall INTEGER DEFAULT 0"); execute(conn,sqlquery)
Now, identify products being recalled. First, create a table containing the new data to write to the database. This table requires only 1 (true) and 0 (false) values.
t = table([1;0],VariableNames="Recall"); head(t)
Create a row filter using the filter condition that a supplier number must be either 1001 or greater than 1005 and the stock number must be greater than 400,000.
rf = rowfilter(["supplierNumber","stockNumber"]); rf = (rf.supplierNumber == 1001 | rf.supplierNumber > 1005) & rf.stockNumber > 400000
rf = RowFilter with constraints:
(supplierNumber == 1001 | supplierNumber > 1005) & stockNumber > 400000VariableNames: supplierNumber, stockNumber
Update the Recall column using this filter to set the values to 1 (true) and using the ~rf value of the filter to set the value to 0 (false).
sqlupdate(conn,"productTable",t,{rf;~rf});
Again, import the data from productTable and display the first 10 rows.
results = sqlread(conn,tablename); head(results,10)
productNumber stockNumber supplierNumber unitCost productDescription Recall
_____________ ___________ ______________ ________ __________________ ______
9 125970 1003 13 "Victorian Doll" 0
8 212569 1001 5 "Train Set" 0
7 389123 1007 16 "Engine Kit" 0
2 400314 1002 9 "Painting Set" 0
4 400339 1008 21 "Space Cruiser" 1
1 400345 1001 14 "Building Blocks" 1
5 400455 1005 3 "Tin Soldier" 0
6 400876 1004 8 "Sail Boat" 0
3 400999 1009 17 "Slinky" 1
10 888652 1006 24 "Teddy Bear" 1 Use the SQL ALTER statement to remove the Recall column from the table.
sqlquery = strcat("ALTER TABLE productTable DROP COLUMN Recall"); execute(conn,sqlquery)
Close the database connection.
Input Arguments
SQLite database connection, specified as an sqlite object created using the sqlite function.
Database table name, specified as a string scalar or character vector denoting the name of a table in the database.
Example: "employees"
Data Types: string | char
data — Updated data
MATLAB table
Updated data, specified as a MATLAB table. The table can contain one or more rows with updated data. The names of the variables in the table must be a subset of the column names of the database table.
Example: data = table([1;0],"VariableNames","NewName")
Data Types: table
filter — Row filter condition
matlab.io.RowFilter object | cell array of matlab.io.RowFilter objects
Row filter condition, specified as a matlab.io.RowFilter object or a cell array of matlab.io.RowFilter objects. Filters determine which database rows sqlupdate must update with which data. If multiple database rows match a filter, sqlupdate updates them with the same data. If a single database row matches multiple filters, its final state matches the data corresponding to the last matching filter.
Example: rf = rowfilter("productnumber"); rf = rf.productnumber <= 5;
Name-Value Arguments
Specify optional pairs of arguments asName1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.
Before R2021a, use commas to separate each name and value, and enclose Name in quotes.
Example: sqlupdate(conn,'inventoryTable',data,rf,Catalog = "toy_store",Schema = "dbo") updates the database inventoryTable stored in thetoy_store catalog and the dbo schema.
Catalog — Database catalog name
string scalar | character vector
Database catalog name, specified as a string scalar or character vector. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have multiple catalogs.
Example: Catalog = "toy_store"
Data Types: string | char
Schema — Database schema name
string scalar | character vector
Database schema name, specified as a string scalar or character vector. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.
Example: Schema = "dbo"
Data Types: string | char
Version History
Introduced in R2023a