sqlupdate

Update rows in database table

Since R2023a

collapse all in page

Syntax

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

Description

sqlupdate(conn,tablename,data,filter) updates rows in the database table tablename, using values from the MATLAB® table data. The update applies only to rows that match the conditions defined in filter.

conn can be any of the following connection objects:

  • MySQL®

  • PostgreSQL®

  • DuckDB™

  • SQLite

  • ODBC

  • JDBC

example

sqlupdate(conn,tablename,Name=Value) specifies additional options using one or more name-value arguments. For example, Catalog="cat" updates data from a database table stored in the catalog cat.

Examples

collapse all

Open Live Script

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
     'string'    <missing>              []
     'string'    <missing>              []
     'double'          NaN              []
     'string'    <missing>              []
     'double'          NaN              []
     'double'          NaN              []
    'logical'            0              []
     'double'          NaN              []
     'double'          NaN              []
     'string'    <missing>              []
    '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)
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
     'string'    1×1 missing              []
     'string'    1×1 missing              []
     'double'            NaN              []
     'string'    1×1 missing              []
     'double'            NaN              []
     'double'            NaN              []
    'logical'              0              []
     'double'            NaN              []
     'double'            NaN              []
     'string'    1×1 missing              []
    '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)
Open Live Script

Create a transient, in-memory DuckDB™ database connection by using the duckdb function.

conn = duckdb();

Create a table to store product data that includes ID, name, and quantity information for three items.

idData = [1001;1002;1003];
nameData = ["item1";"item2";"item3"];
quantityData = [250;100;400];
varNames = ["ID","Name","Quantity"];
productData = table(idData,nameData,quantityData,VariableNames=varNames)
productData=3×3 table
     ID      Name      Quantity
    ____    _______    ________

    1001    "item1"      250   
    1002    "item2"      100   
    1003    "item3"      400

Insert the product data into the DuckDB database and store it in table named dbTable.

sqlwrite(conn,"dbTable",productData);

Import dbTable from the DuckDB database using the sqlread function. Verify that the values in data match the values in dbTable.

data = sqlread(conn,"dbTable")
data=3×3 table
     ID      Name      Quantity
    ____    _______    ________

    1001    "item1"      250   
    1002    "item2"      100   
    1003    "item3"      400

Create another table that has the name of a new product.

newData = table("newName",VariableNames="Name")
newData=table
      Name   
    _________

    "newName"

Update the product name in dbTable for the row where ID=1002. Use the rowfilter function with the value from the newName table to apply a filter that matches ID 1002.

rf = rowfilter("ID");
rf = rf.ID==1002;
sqlupdate(conn,"dbTable",newData,rf);

Confirm that the product name was updated by using the sqlread function.

sqlread(conn,"dbTable")
ans=3×3 table
     ID       Name       Quantity
    ____    _________    ________

    1001    "item1"        250   
    1002    "newName"      100   
    1003    "item3"        400

Close the database connection.

close(conn);
Open Live Script

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)
Open Live Script

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
       'char'     ''              []
       'char'     ''              []
     'double'    NaN              []
       'char'     ''              []
     'double'    NaN              []
     'double'    NaN              []
     'double'    NaN              []
     'double'    NaN              []
     'double'    NaN              []
       'char'     ''              []
    '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

collapse all

Database connection, specified as any of the following:

  • MySQL connection object created by using the mysql function.

  • PostgreSQL connection object created by using the postgresql function.

  • DuckDB connection object created by using the duckdb function.

  • SQLite connection object created by using the sqlite function.

  • ODBC connection object created by using the database function.

  • JDBC connection object created by 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

Data to update, specified as a MATLAB table. The names of the variables in data 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. Use a row filter to determine which rows in the database are updated and which data is applied. sqlupdate updates existing rows in a table based on these filter rules:

  • If multiple rows match a single filter, all those rows are updated with the same data.

  • If one row matches more than one filter, its final values come from the data associated with the last matching filter.

Example: rf = rowfilter("productnumber"); rf = rf.productnumber <= 5;

Name-Value Arguments

collapse all

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 inventoryTable database 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

expand all

See Also

| | | | | | | | | | |