sqlupdate

Update rows in SQLite database table

Since R2023a

Syntax

sqlupdate(conn,tablename,data,filter)

sqlupdate(___,Name,Value)

Description

sqlupdate(conn,tablename,data,filter) updates rows in the SQLite 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 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)

Input Arguments

conn — SQLite database connection

sqlite object

SQLite database connection, specified as an sqlite object created using the sqlite 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 a 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