sqlupdate

Update rows in MySQL database table

Since R2023a

Syntax

sqlupdate(conn,tablename,data,filter)

sqlupdate(___,Name,Value)

Description

sqlupdate(conn,tablename,data,filter) updates rows in the MySQL^® database table (tablename) with the rows from the MATLAB^® table (data) based on filter conditions (filter).

sqlupdate(___,Name,Value) 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 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