Updating Data with Raw SQL
The raw SQL command UPDATE updates data in a database. An alternative is to use the Wolfram Language command SQLUpdate, described in "Updating Data".
If you find that the examples in this tutorial do not work as shown, you may need to install or restore the example database with the "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".
When updating data, the result of SQLExecute is an integer specifying the number of rows affected by the query.
Here is an example that updates data. This loads DatabaseLink and connects to the demo database.
Needs["DatabaseLink`"];
conn = OpenSQLConnection["demo"];As discussed in "Creating Tables with Raw SQL", the ADDRESSES and MAILER tables should be created.
SQLExecute[conn,
"CREATE TABLE ADDRESSES (
USERNAME VARCHAR(64) NOT NULL PRIMARY KEY,
ADDRESS VARCHAR(64),
CITY VARCHAR(64),
ZIPCODE VARCHAR(16),
UNIQUE (ADDRESS, CITY, ZIPCODE))"];
SQLExecute[conn, "CREATE TABLE MAILER (
MAILERID INT IDENTITY,
USERNAME VARCHAR(64) NOT NULL,
SENDMAILER BIT DEFAULT '1' NOT NULL,
FOREIGN KEY (USERNAME) REFERENCES ADDRESSES (USERNAME))"];
SQLExecute[conn, "INSERT INTO ADDRESSES (USERNAME, ADDRESS, CITY, ZIPCODE) VALUES (`1`)",
{{SQLArgument["user1", "100 Trade Center", "Champaign, IL", "61820"]}, {SQLArgument["user2", "200 Trade Center", "Champaign, IL", "61820"]}, {SQLArgument["user3", "300 Trade Center", "Champaign, IL", "61820"]}}
];
SQLExecute[conn, "INSERT INTO MAILER (MAILERID, USERNAME, SENDMAILER) VALUES (`1`)",
{{SQLArgument[Null, "user1", False]},
{SQLArgument[Null, "user2", False]}}
];This executes an SQL statement that updates a row in the MAILER table. This query updates the SENDMAILER column based on the value of USERNAME. Many update statements may be created using conditions that work with values in columns.
SQLExecute[conn,
"UPDATE MAILER SET SENDMAILER = 1 WHERE USERNAME = 'user1'"]A SELECT statement verifies that the data has been changed in the table.
SQLExecute[conn, "SELECT * FROM MAILER"]Using prepared statements, you can dynamically create SQL statements that update data within the database. You can combine this with a simple Wolfram Language function. This example updates the address for a particular user.
SetAddress[username_String, address_String] :=
SQLExecute[conn,
"UPDATE ADDRESSES SET ADDRESS = `2` WHERE USERNAME = `1`", {username, address}]SetAddress["user1", "100 Trade Center Office 123"]A SELECT statement verifies that the data has been changed in the table.
SQLExecute[conn, "SELECT * FROM ADDRESSES"]The same restrictions that apply to inserts also apply to updates. Thus, if you try to update an ADDRESS value to equal the ADDRESS value of another row, an error will be returned; this table requires them to be unique.
SetAddress["user1", "200 Trade Center"]
This deletes the tables and closes the connection.
SQLExecute[conn, "DROP TABLE MAILER"];
SQLExecute[conn, "DROP TABLE ADDRESSES"];
CloseSQLConnection[conn];