sqlupdate
Description
sqlupdate(___,
specifies additional options using one or more name-value arguments with any of the previous
input argument combinations. For example, Name,Value
)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)
Recall ______ 1 0
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 > 400000 VariableNames: 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.
close(conn)
Input Arguments
conn
— SQLite database connection
sqlite
object
SQLite database connection, specified as an sqlite
object created using the sqlite
function.
tablename
— Database table name
string scalar | character vector
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 as
Name1=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 the
toy_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
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)