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 MySQL native interface database based on filter conditions specified with row filters.
This example uses the patients.xls
file, which contains the columns LastName
, Gender
, Age
, Location
, Height
, Weight
, Smoker
, Systolic
, Diastolic
, and SelfAssessedHealthStatus
. The example also uses a MySQL database version 5.7.22 with the MySQL Connector/C++ driver version 8.0.15.
Create a MySQL native interface database connection to a MySQL database.
datasource = "MySQLNative"; username = "root"; password = "matlab"; conn = mysql(datasource,username,password);
Load patient information into the MATLAB workspace.
patients = readtable("patients.xls");
Create the patients
database table using the patient information.
tablename = "patients";
sqlwrite(conn,tablename,patients)
Use the SQL ALTER
statement to add the column HighRisk
to the table patients
.
sqlquery = 'ALTER TABLE patients ADD HighRisk bit';
execute(conn,sqlquery)
Import the patients
database table using the sqlread
function, and return metadata information about the imported data.
[data,metadata] = sqlread(conn,tablename);
Display the first 10 rows of the table. In MATLAB, all the values in the HighRisk
column appear as false
.
head(data,10)
LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus HighRisk __________ ________ ___ ___________________________ ______ ______ ______ ________ _________ ________________________ ________ "Smith" "Male" 38 "County General Hospital" 71 176 true 124 93 "Excellent" false "Johnson" "Male" 43 "VA Hospital" 69 163 false 109 77 "Fair" false "Williams" "Female" 38 "St. Mary's Medical Center" 64 131 false 125 83 "Good" false "Jones" "Female" 40 "VA Hospital" 67 133 false 117 75 "Fair" false "Brown" "Female" 49 "County General Hospital" 64 119 false 122 80 "Good" false "Davis" "Female" 46 "St. Mary's Medical Center" 68 142 false 121 70 "Good" false "Miller" "Female" 33 "VA Hospital" 64 142 true 130 88 "Good" false "Wilson" "Male" 40 "VA Hospital" 68 180 false 115 82 "Good" false "Moore" "Male" 28 "St. Mary's Medical Center" 68 183 false 115 78 "Excellent" false "Taylor" "Female" 31 "County General Hospital" 66 132 false 118 86 "Excellent" false
Displaying the metadata shows that the values are NULL
(missing elements) in the database.
metadata
metadata=11×3 table
VariableType FillValue MissingRows
____________ _____________ ______________
LastName {'string' } {[<missing>]} { 0×1 double}
Gender {'string' } {[<missing>]} { 0×1 double}
Age {'double' } {[ NaN]} { 0×1 double}
Location {'string' } {[<missing>]} { 0×1 double}
Height {'double' } {[ NaN]} { 0×1 double}
Weight {'double' } {[ NaN]} { 0×1 double}
Smoker {'logical'} {[ 0]} { 0×1 double}
Systolic {'double' } {[ NaN]} { 0×1 double}
Diastolic {'double' } {[ NaN]} { 0×1 double}
SelfAssessedHealthStatus {'string' } {[<missing>]} { 0×1 double}
HighRisk {'logical'} {[ 0]} {100×1 double}
Now, identify patients who are considered high risk for developing some hypothetical health issue based on their age and their smoker status. 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","HighRisk"); head(t)
HighRisk ________ 1 0
Create a row filter using the filter condition that a patient must be older than 35 years and a smoker to be considered high-risk.
rf = rowfilter(["Age","Smoker"]); rf = rf.Age > 35 & rf.Smoker == 1
rf = RowFilter with constraints: Age > 35 & Smoker == 1 VariableNames: Age, Smoker
Update the HighRisk
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,"patients",t,{rf;~rf});
Again, import the patients
database table using the sqlread
function, and display the first 10 rows.
data = sqlread(conn,tablename); head(data,10)
LastName Gender Age Location Height Weight Smoker Systolic Diastolic SelfAssessedHealthStatus HighRisk __________ ________ ___ ___________________________ ______ ______ ______ ________ _________ ________________________ ________ "Smith" "Male" 38 "County General Hospital" 71 176 true 124 93 "Excellent" true "Johnson" "Male" 43 "VA Hospital" 69 163 false 109 77 "Fair" false "Williams" "Female" 38 "St. Mary's Medical Center" 64 131 false 125 83 "Good" false "Jones" "Female" 40 "VA Hospital" 67 133 false 117 75 "Fair" false "Brown" "Female" 49 "County General Hospital" 64 119 false 122 80 "Good" false "Davis" "Female" 46 "St. Mary's Medical Center" 68 142 false 121 70 "Good" false "Miller" "Female" 33 "VA Hospital" 64 142 true 130 88 "Good" false "Wilson" "Male" 40 "VA Hospital" 68 180 false 115 82 "Good" false "Moore" "Male" 28 "St. Mary's Medical Center" 68 183 false 115 78 "Excellent" false "Taylor" "Female" 31 "County General Hospital" 66 132 false 118 86 "Excellent" false
Delete the patients
database table using the execute
function.
sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)
Close the database connection.
close(conn)
Input Arguments
conn
— MySQL native interface database connection
connection
object
MySQL native interface database connection, specified as a connection
object. Starting in R2024a, it is recommended that you use setSecret
and getSecret
to store and retrieve your credentials for databases that require authentication. For
more details, refer to this
example.
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 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
See Also
sqlfind
| fetch
| sqlread
| sqlinnerjoin
| sqlouterjoin
| mysql
| close
| execute
| databaseImportOptions
| setoptions
| getoptions
| reset
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)