Main Content

sqlupdate

Update rows in PostgreSQL database table

Since R2023a

collapse all in page

Syntax

sqlupdate(conn,tablename,data,filter)
sqlupdate(___,Name,Value)

Description

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

example

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

collapse all

Open Live Script

Update rows in the PostgreSQL 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 uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.

Create a PostgreSQL native interface database connection to a PostgreSQL database.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";

conn = postgresql(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 boolean'; 
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' }     {1×1 missing}    {  0×1 double}
    gender                      {'string' }     {1×1 missing}    {  0×1 double}
    age                         {'double' }     {[      NaN]}    {  0×1 double}
    location                    {'string' }     {1×1 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' }     {1×1 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 true and false values.

t = table([true;false],"VariableNames","HighRisk");
head(t)
    HighRisk
    ________

     true   
     false

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 == true
rf = 
  RowFilter with constraints: 

    Age > 35 & Smoker == true

  VariableNames: Age, Smoker

Update the HighRisk column using this filter to set the values to true and using the ~rf value of the filter to set the value to 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   
    "White"       "Male"      39     "VA Hospital"                  72       202      true        130          95              "Excellent"            true   
    "Martin"      "Male"      48     "VA Hospital"                  71       181      true        130          92              "Good"                 true   
    "Lee"         "Female"    44     "County General Hospital"      66       146      true        128          90              "Fair"                 true   
    "Wright"      "Female"    45     "VA Hospital"                  70       126      true        134          92              "Excellent"            true   
    "Baker"       "Male"      44     "VA Hospital"                  71       192      true        136          90              "Good"                 true   
    "Mitchell"    "Male"      39     "County General Hospital"      71       164      true        128          92              "Fair"                 true   
    "Roberts"     "Male"      44     "VA Hospital"                  70       169      true        132          89              "Good"                 true   
    "Turner"      "Male"      37     "VA Hospital"                  70       194      true        137          96              "Excellent"            true   
    "Collins"     "Male"      42     "County General Hospital"      67       179      true        124          78              "Good"                 true

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

collapse all

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

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

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.

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

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

| | | | | | | | | | |