Execute update query

This restriction applies whether you use an update query or try to update data manually by entering values in a form or a datasheet. Fields in a union query You cannot update data from fields in a union query because each record that appears in two or more data sources only appears once in the union query result. Because some duplicate records are removed from the results, Access cannot update all the necessary records.

Fields that are primary keys In some cases, such as if the primary key field is used in a table relationship, you cannot update the field by using a query unless you first set the relationship to automatically cascade updates. Note: When you cascade updates, Access automatically updates foreign key values when you change a primary key value in a parent table. As a best practice when creating an update query, first create a select query that identifies the records that you want to update, and then convert that query to an update query that you can run to update the records.

Tip: Back up your database before you run an update query. You cannot undo the results of an update query, and making a backup helps make sure that you can reverse your changes if you change your mind. Click the File Tab, and then click Save As. In the Save Backup As dialog box, specify a name and location for the backup copy, and then click Save.

To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and then open the renamed backup copy in Access. Step 1: Create a select query to identify the records to update.

Step 2: Update the records. On the Create tab, in the Queries group, click Query Design. Select the table or tables that contain the records that you want to update, click Add , and then click Close. The table or tables appear as one or more windows in the query designer, and the windows list all the fields in each table. This figure shows the query designer with a typical table. Double-click the fields that you want to update in the table windows.

The selected fields appear in the Field row in the query design grid. This figure shows the query design grid with all the fields added.

To limit the query results based on field values, in the query design grid, in the Criteria row, enter the criteria that you want to use to limit the results.

The following table shows some example criteria and explains the effect that they have on the results of a query. Note: Many of the examples in this table use wildcard characters to make the query more flexible or powerful. If your database uses the ANSI wildcard characters, use single quotation marks ' instead of pound signs. Finds all records where the exact contents of the field are not exactly equal to "Germany. Finds all records except those starting with T.

Finds all records that do not end with t. In a text field, finds all records that start with the letters A through D. Finds all records that include the letter sequence "ar". Finds all records that begin with "Maison" and contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown. Finds all records for February 2, Uses the Date function to return all records containing today's date.

Uses the Date and DateAdd functions to return all records between today's date and three months from today's date. Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what that value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, you enter a pair of double quotation marks with no space between them "" instead of a number.

On the Design tab, in the Results group, click Run. To add any fields that you want to include in the query design, drag the additional fields to the query design grid. On the Design tab, in the Query Type group, click Update. This procedure shows you how to change a select query to an update query.

When you do this, Access adds the Update to row in the query design grid. The following illustration shows an update query that returns all the assets purchased after January 5, and changes the location to "Warehouse 3" for all the records that meet that criterion.

Locate the field that contains the data that you want to change, and then type your expression your change criteria in the Update to row for that field. Where the ProductID values in the current table match the ProductID values in table named Order Details, this expression updates sales totals by multiplying the values in a field named Quantity by the values in a field named UnitPrice.

The expression uses the DSum function because it can operate against more than one table and table field. Truncates removes the leftmost characters in a text or numeric string and leaves the 5 rightmost characters. Note: When you run the query, you might notice that some fields are missing from your result set. If your query contains fields that you don't update, Access does not display those fields in the results, by default. For example, you might include ID fields from two tables to help ensure that your query identifies and updates the correct records.

If you don't update those ID fields, Access does not display them in the results. When you need to update data from one table to another, consider the following rule: the data types for the source and destination fields must either match or be compatible. Furthermore, when you update data from one table to another and use compatible data types instead of matching data types, Access converts the data types of those fields in the destination table. As a result, some of the data in the destination fields may be truncated deleted.

The section Restrictions on data type conversions lists the ways in which you can and cannot convert data types. The table in this section also explains when converting a data type can change or eliminate some or all the data in a field, and what data might be eliminated. Add the names of your destination fields to the Field row of the query design grid. The steps in this section assume the use of two similar tables.

In this example, the Clients table is located in a database that you just inherited, and it contains more recent data than the Customers table. You can see that some of the manager names and addresses have changed. For that reason, you decide to update the Customers table with the data from the Clients table.

As you continue, remember that although the data types for each table field do not have to match, they must be compatible. Access must be able to convert the data in the source table into a type that the destination table can use.

In some cases, the conversion process might delete some data. For more information about restrictions when you convert data types, see the section Restrictions on data type conversions.

Note: The following steps assume the use of the two preceding sample tables. You can adapt the steps to fit your data. Double-click your source and destination tables to add them to the query. Each table appears in a window in the query designer. In most cases, Access automatically joins related fields in a query.

To manually join fields that contain related information, drag the related field from one table to the equivalent field in the other table. Access creates a relationship between those fields in the two tables and uses that relationship to join any related records. In the destination table, double-click the fields that you want to update.

Each field appears in the Field row in the query design grid. If you use the sample tables, you add all fields except the Customer ID field. Notice that the name of the destination table appears in the Table row of the design grid. In the Update To row of the query, in each of the columns that contains a destination field, add the name of the source table and the field in the source table that corresponds to the field in the destination table, and make sure that you use this syntax: [ Table ].

This figure shows part of the design grid, using the sample tables. Note the syntax for the table and field names in the Update To row. As you continue, remember that you must spell the table and field names in the Update To row correctly, and you must match any punctuation in the original table and field names.

However, you do not have to match capitalization. The following table lists the data types that Access provides, explains any restrictions on how to convert data types, and briefly describes any data loss that might occur during conversion.

I have a question related to this method: st. Directly from this oracle java tutorial:. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement. What is meant by " one or more ResultSet objects "? How is it possible to manage them once got an array of ResultSet? Whereas st.

It's not at least to me the aim of st. The best place to find answers to questions like this is the Javadocs: Here. Normally you can ignore this unless you are 1 executing a stored procedure that you know may return multiple results or 2 you are dynamically executing an unknown SQL string.

That pretty much explains it. Sometimes a query can deliver more than one ResultSet. One use of execute is to execute an SQL statement if you don't know if it is a query, an update of some kind It is a generalization of executeQuery and executeUpdate This method returns the ResultSet object that returns the data according to the query.

How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 8 years, 8 months ago. Active 4 months ago.

Viewed 45k times. Directly from this oracle java tutorial: execute: Returns true if the first object that the query returns is a ResultSet object. Thanks in advance.