Modifying Data Using Queries

If you need to modify many rows, it is often easier to create a query that changes, inserts, deletes, or copies rows all in one operation than to edit the rows individually in the Results pane.

Types of Queries for Modifying Data

Using the graphical panes of the Query Designer, you can create these types of queries:

• Update query   Changes the values of individual columns in one or more existing rows in a table. This type of query creates an SQL UPDATE statement. For details, see Creating Update Queries.
  
  
• Insert Values query   Creates a new row and inserts values into specified columns. This type of query creates an SQL INSERT INTO…VALUES statement. For details, see Creating Insert Values Queries.
  
  
• Insert query   Creates new rows by copying existing rows from one table into another, or into the same table as new rows. This type of query creates an SQL INSERT…SELECT statement. For details, see Creating Insert Queries.
  
  
• Make Table query   Creates a new table and creates rows in it by copying the results of a query into it. This type of query creates an SQL SELECT…INTO statement. For details, see Creating Make Table Queries.

  Note   In Oracle, a Make Table query is implemented as a Create Table As Select command. For details, see Query Designer Considerations for Oracle Databases.

• Delete query   Removes one or more rows from a table. This type of query creates an SQL DELETE statement. For details, see Creating Delete Queries.

  Note   A Delete query removes entire rows from the input source. If you want to delete values from individual data columns, use an Update query. If you want to delete columns from a table definition, use the Database Designer. For information on using the Database Designer, see Database Designer.

In addition to using the queries listed above, you can enter any SQL statement into the SQL pane. When you do, the Query Designer dims the graphical panes to indicate that they do not reflect the query you are creating.

In most databases, you can use queries to update or delete rows in only one table. Therefore, the Query Designer displays only one input source window in the Diagram pane. Some databases allow you to update or delete rows in joined tables. In such cases, you must enter your query in the SQL pane, and the graphical panes will be dimmed.

Note   You cannot update or insert timestamp or BLOB columns using the Query Designer.

You can edit rows in a multiuser environment, but your changes might conflict with those made by another user.

Constraints and Referential Integrity

When you update a table using the Query Designer, the query respects any constraint defined in the database for the columns and tables that you are updating. For example, if a table is defined with a unique constraint, you cannot execute an Update or Insert query that would write a non-unique row to the table. Similarly, if you are updating a column that has a constraint limiting its values to a certain range, the query will result in an error if you provide an update value outside the specified range.

Queries also respect referential integrity defined between tables. For example, you cannot add rows to a table in the "many" side of a one-to-many relationship if the corresponding "one" side row does not exist.

Note   If you are using a database such as Oracle or Microsoft Access that supports cascaded deletes, deleting a row with a one-to-many relationship to another table, deleting a row from the table in the "one" side will also delete rows from the table in the "many." For details, refer to the documentation for your database.