sqlupdate

Update rows in database table

Since R2023a

Syntax

sqlupdate(conn,tablename,data,filter)

sqlupdate(___,Name,Value)

Description

sqlupdate(conn,tablename,data,filter) updates rows in the 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 database rows 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 Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

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        1         124          93             {'Excellent'}           false  
    {'Johnson' }    {'Male'  }    43     {'VA Hospital'              }      69       163        0         109          77             {'Fair'     }           false  
    {'Williams'}    {'Female'}    38     {'St. Mary's Medical Center'}      64       131        0         125          83             {'Good'     }           false  
    {'Jones'   }    {'Female'}    40     {'VA Hospital'              }      67       133        0         117          75             {'Fair'     }           false  
    {'Brown'   }    {'Female'}    49     {'County General Hospital'  }      64       119        0         122          80             {'Good'     }           false  
    {'Davis'   }    {'Female'}    46     {'St. Mary's Medical Center'}      68       142        0         121          70             {'Good'     }           false  
    {'Miller'  }    {'Female'}    33     {'VA Hospital'              }      64       142        1         130          88             {'Good'     }           false  
    {'Wilson'  }    {'Male'  }    40     {'VA Hospital'              }      68       180        0         115          82             {'Good'     }           false  
    {'Moore'   }    {'Male'  }    28     {'St. Mary's Medical Center'}      68       183        0         115          78             {'Excellent'}           false  
    {'Taylor'  }    {'Female'}    31     {'County General Hospital'  }      66       132        0         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                    {'char'   }     {0×0 char}    {  0×1 double}
    Gender                      {'char'   }     {0×0 char}    {  0×1 double}
    Age                         {'double' }     {[   NaN]}    {  0×1 double}
    Location                    {'char'   }     {0×0 char}    {  0×1 double}
    Height                      {'double' }     {[   NaN]}    {  0×1 double}
    Weight                      {'double' }     {[   NaN]}    {  0×1 double}
    Smoker                      {'double' }     {[   NaN]}    {  0×1 double}
    Systolic                    {'double' }     {[   NaN]}    {  0×1 double}
    Diastolic                   {'double' }     {[   NaN]}    {  0×1 double}
    SelfAssessedHealthStatus    {'char'   }     {0×0 char}    {  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        1         124          93             {'Excellent'}           true   
    {'Johnson' }    {'Male'  }    43     {'VA Hospital'              }      69       163        0         109          77             {'Fair'     }           false  
    {'Williams'}    {'Female'}    38     {'St. Mary's Medical Center'}      64       131        0         125          83             {'Good'     }           false  
    {'Jones'   }    {'Female'}    40     {'VA Hospital'              }      67       133        0         117          75             {'Fair'     }           false  
    {'Brown'   }    {'Female'}    49     {'County General Hospital'  }      64       119        0         122          80             {'Good'     }           false  
    {'Davis'   }    {'Female'}    46     {'St. Mary's Medical Center'}      68       142        0         121          70             {'Good'     }           false  
    {'Miller'  }    {'Female'}    33     {'VA Hospital'              }      64       142        1         130          88             {'Good'     }           false  
    {'Wilson'  }    {'Male'  }    40     {'VA Hospital'              }      68       180        0         115          82             {'Good'     }           false  
    {'Moore'   }    {'Male'  }    28     {'St. Mary's Medical Center'}      68       183        0         115          78             {'Excellent'}           false  
    {'Taylor'  }    {'Female'}    31     {'County General Hospital'  }      66       132        0         118          86             {'Excellent'}           false  

Delete the patients database table using the execute function.

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)

Input Arguments

conn — Database connection

connection object

Database connection, specified as an ODBC connection object or JDBC connection object created using the database 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 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