xfODBC supports the following SQL statements that modify data. For links to more information on xfODBC’s support for SQL, see Appendix B: SQL Support.
|
For updating Synergy databases, we strongly recommend using a Synergy application that’s designed to efficiently maintain database integrity. If you use an ODBC-enabled application to update a Synergy database, you may run into record-locking issues. We strongly recommend that you prevent applications that use the Microsoft Jet database engine (including Microsoft Access and Query) from updating Synergy databases. Failure to do so may result in unsupportable situations. These applications often have record locking issues (they may lock more than just the record that’s being updated), and there are often no referential checks or triggers to ensure database integrity. In addition, these applications may allow users to make bulk changes without your control. (Different versions of Jet will give different results; we recommend at least Jet 4 service pack 8. Synergy/DE installations do not update Jet.) |
DELETE
This command deletes a row, a group of rows, or all rows in a table.
DELETE FROM table_name [table_alias] [WHERE search_condition][WHERE CURRENT OF cursor]
where
table_name is the name of a table or view.
table_alias is an alias.
search_condition is the selection criteria for the rows.
cursor is ignored.
You can use DELETE with a view if the view is for a single table. Deleting a row from a view deletes the row from the base table.
For example, the first statement below deletes all rows in mytable. The second deletes only rows that meet the WHERE clause criteria.
DELETE FROM mytable DELETE FROM mytable WHERE col_1 > 2 OR col_2 < 4
WHERE CURRENT OF clauses are ignored.
INSERT
This command inserts one or more rows into a table.
INSERT INTO table_name [(col_1[, col_2, ...])] VALUES (value_1[, value_2...])
or
INSERT INTO table_name [(col_1[, col_2, ...])] subquery
where
table_name is the name of the table or single-table view that the rows will be added to.
col_# are columns in table_name that values will be specified for.
value_# are values to be inserted.
subquery is a SELECT statement whose results will be inserted.
The first form of INSERT inserts a single row into a table. The second form inserts as many rows as are returned by the subquery. With either form, you must use single quotes to specify string values.
You can use INSERT with a view if the view is for a single table. Inserting a row into a view inserts the row into the base table.
If you don’t list columns (i.e., if you omit col_1, col_2, ...), you must supply a value for each column in the table (table_name). Either include a value_# for each column, or make sure the select list for subquery includes an item for each column. The order of values (value_#) or subquery select list items must match the order of the columns in table_name. For example, the following includes a value_# for each column in the ORDERS table for the sample database:
INSERT INTO orders VALUES (11, 42, 1, 24, 5, 2.55, '01', 1993-04-18, null, 2000-10-01, 586455)
If you do list columns (col_1, col_2, ...), the specified values (value_#) or the items in the select list for subquery must correspond to the specified columns. For example:
INSERT INTO mytable (col_1, col_2) VALUES (1, 1) INSERT INTO orders (or_number, or _item, or_price) SELECT 12, in_itemid, in_price FROM plants WHERE in_name = 'Wedelia'
Note that if you list columns, columns you leave out of the list are set to null. So if a column can’t accept null values, you must include it in the column list. And if table_name includes an overlay column that is not read-only and is not set by the INSERT, the overlay column is set to null, which sets all fields included in the overlay to null. To prevent this, set overlay columns to read-only by setting their corresponding repository fields to read-only. Then regenerate the system catalog.
UPDATE
This command changes a single row, groups of rows, or all the rows in a table.
UPDATE table_name [table_alias] SET column = value|subquery [[, column = value|subquery]...] [WHERE condition]
where
table_name is the is the name of a table or a single-table view. If table_name is a view, the underlying table will be updated.
table_alias is an alias for the table or view.
column is a column in table_name.
value is the value (or an expression that results in a value) that column will be set to.
subquery is a subquery whose results will be used to update column. Note that subqueries in SET clauses must use SELECT DISTINCT to ensure only one value is returned for the column.
condition is the criteria used to determine if a row will be updated.
You can specify the rows you want to change and a new value. The new value can be a constant or an expression.
For example:
UPDATE mytable SET col_3 = 'Fine' UPDATE mytable SET col_3 = 'Fine' WHERE col_1 > 2 UPDATE mytable SET col_2 = 3, col_3 = 'Fine' WHERE col_2 > 2 UPDATE orders SET or_vendor = (SELECT DISTINCT vend_key FROM vendors WHERE vend_name = 'Border Imports'), (or_item, or_price) = (SELECT DISTINCT in_itemid, in_price FROM plants WHERE in_name = 'Wedelia') WHERE or_number = 3
For Synergy ISAM files, you can’t use UPDATE to change a value in a non-modifiable key column. To change a value in a key column, you must delete the row and then insert a new row that contains the change.
