Roll Back Data After Updating Record

This example shows how to update data in a database and roll back the changes. Rolling back the changes reinstates the data as it appears before running the update.

Create a database connection conn. For example, the following code uses the database toy_store, user name username, password pwd, server name sname, and port number 123456 to connect to a Microsoft^® SQL Server^® database. This database contains the table inventoryTable that contains these columns: productNumber, Quantity, and Price.

conn = database('toy_store','username','pwd',...
                'Vendor','Microsoft SQL Server',...
                'Server','sname',...
                'PortNumber',123456);

Set the AutoCommit property of the connection object to 'off'. Any updates you make after turning off this flag do not commit to the database automatically.

conn.AutoCommit = 'off';

Display the data in the inventoryTable table before making updates. Import the data from the executed query using the fetch function and display the first few rows of imported data.

d = fetch(conn,'SELECT * FROM inventoryTable');
head(d)

ans =

  8×4 table

    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

          1            1700      14.5     '2014-09-23 09:38:34'
          2            1200         9     '2014-07-08 22:50:45'
          3             356        17     '2014-05-14 07:14:28'
          4            2580        21     '2013-06-08 14:24:33'
          5            9000         3     '2012-09-14 15:00:25'
          6            4540         8     '2013-12-25 19:45:00'
          7            6034        16     '2014-08-06 08:38:00'
          8            8350         5     '2011-06-18 11:45:35'

Define a cell array for the new price of the first product.

data(1,1) = {30.00};

Define the WHERE clause for the first product.

whereclause = 'where productNumber = 1';

Update the Price column in the inventoryTable for the first product.

tablename = 'inventoryTable';
colname = {'Price'};

update(conn,tablename,colname,data,whereclause)

Display the data in the inventoryTable table after making the update.

d = fetch(conn,'SELECT * FROM inventoryTable');
head(d)

ans =

  8×4 table

    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

          1            1700        30     '2014-09-23 09:38:34'
          2            1200         9     '2014-07-08 22:50:45'
          3             356        17     '2014-05-14 07:14:28'
          4            2580        21     '2013-06-08 14:24:33'
          5            9000         3     '2012-09-14 15:00:25'
          6            4540         8     '2013-12-25 19:45:00'
          7            6034        16     '2014-08-06 08:38:00'
          8            8350         5     '2011-06-18 11:45:35'

The first product has an updated price of 30. Though the data is updated, the change has not committed to the database.

Roll back the update.

rollback(conn)

Alternatively, you can roll back the update using an SQL ROLLBACK statement by using the execute function.

Display the data in the inventoryTable table after rolling back the update.

d = fetch(conn,'SELECT * FROM inventoryTable');
head(d)

ans =

  8×4 table

    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

          1            1700      14.5     '2014-09-23 09:38:34'
          2            1200         9     '2014-07-08 22:50:45'
          3             356        17     '2014-05-14 07:14:28'
          4            2580        21     '2013-06-08 14:24:33'
          5            9000         3     '2012-09-14 15:00:25'
          6            4540         8     '2013-12-25 19:45:00'
          7            6034        16     '2014-08-06 08:38:00'
          8            8350         5     '2011-06-18 11:45:35'

The first product has the old price of 14.50.

Close the database connection.

close(conn)