Update Statements

The UPDATE statement is used to update values in the database. UPDATE statements have the ability to update multiple records at a time depending on the search constraints given.
Here is an example of an UPDATE statement:

UPDATE <Table Name> SET <field1> = <value1> [,<field2>=<value2>...] where <search conditions>

The following example identifies a single record by its _rowid column and changes the FIRST_NAME value to Bill.

UPDATE TALENT SET FIRST_NAME = 'Bill' WHERE _rowid =100

UPDATE is a keyword that tells the database that you want to update the values in existing records.

TALENT is the name of the table whose records will be updated.

SET is a keyword that tells the database that the following list will be a list of columns and corresponding values.

FIRST_NAME = Bill means that every record in the result will have its first name set to Bill.

WHERE is a keyword that specifies that there will be a list of search conditions.

_rowid = 100 is the search condition that identifies a single record by its _rowid column.

The following example updates multiple records with one SQL statement. Lets say that the rating has been changed from G to PG for all movies with a drama category. The LANGUAGE field in this example does not have to be updated (let s say that 100 is the code for English Language), but we update it anyway to demonstrate that multiple changes can be specified by separating them with commas.

UPDATE MOVIE SET RATED='PG', LANGUAGE = 100 WHERE CATEGORY = 'Drama' AND RATING = 'G'

UPDATE is a keyword that tells the database that you want to update the values in existing records.

MOVIE is the name of the table whose records will be updated.

SET is a keyword that tells the database that the following list will be a list of columns and corresponding values.

RATED= PG, means that every record in the result will have its RATED column changed to PG.

LANGUAGE= 100 means that every record in the result will have its LANGUAGE column value changed to 100.

WHERE is a keyword that specifies that there will be a list of search conditions.

CATEGORY = Drama AND RATED = G tells the database that you are looking for records whose CATEGORY is Drama and RATED is G.

Using Subqueries in Updates

Sometimes it is necessary to update records that match values in other tables. For this you can use a subquery in the where statement. Here is an example that uses an IN clause to update the values in one table based on the value of a matching record in another.

UPDATE MOVIE SET RATED='PG' WHERE STUDIO_ID IN (SELECT STUDIO.STUDIO_ID FROM STUDIO WHERE STUDIO.NAME = ‘MGM’

Safe SQL Mode

OpenBase provides a “safe SQL Mode” through the database configure panel which will require all update statements to have a where clause. This is a safe-guard when you have a database that could be effected by users mistyping SQL statements.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License