Adding and removing tables and columns

You can add one or more tables or views to your SQL statement, depending on the type of statement that you are creating.

You can add or remove tables or relational views at any time while working on your statement. For information on creating relational views, see Creating relational views.

You can create an SQL statement on multiple tables or views using SELECT, WITH, and FULLSELECT statements. UPDATE, INSERT and DELETE statements operate on a single table or view.

Using the SQL Builder

To add a table or relational view to your statement using the SQL Builder:

  1. Switch to the Data perspective. Click Window > Open Perspective > Other, and then double-click Data in the Select Perspective window.

  2. Create a new statement, if required, or open an existing statement.

  3. Right-click in the Tables pane of the SQL Builder, and then click Add Table on the pop-up menu.

  4. In the Table name list, click the table to add. You can also select a view in this list.

  5. (Optional) Create an alias for the table.

  6. Click OK.

  7. To add more tables or views (SELECT statement only), repeat steps 3-6.

    Note: Instead of using the pop-up menu in the Tables pane to add a table, you can drag a table from the database in the Data Definition view to the Tables pane.

The tables or views are added to the Tables pane and reflected in the SQL source.

Note: If you rearrange the table graphics in the Tables pane and then save the SQL statement, the arrangement of the table graphics reverts back to the linear format and the order of the table graphics reverts to their order in the SQL statement.

To remove a table or relational view, right-click the table in the Tables pane and then click Remove Table on the pop-up menu. When you remove a table or relational view, any expressions or clauses in the statement that reference that table's columns are deleted.

To update a table's alias, right-click the table in the Tables pane and then click Update Alias on the pop-up menu.

To work with specific columns, select the appropriate check boxes in the table (in the Tables pane) for those columns.

Using the Create A New SQL Statement wizard

To add a table or relational view to your SQL statement using the wizard:

  1. Click File > New > Other.

  2. In the New window, expand the Data folder, click SQL Statement, and then click Next.

  3. Fill in the fields as necessary (for information about a field, place the pointer in the field and then press F1). To be taken through the steps of creating a statement select the Be guided through creating an SQL statement radio button. Click Next.

  4. On the Tables page of the wizard, select a table or relational view from the Available Tables list, and then click the > button. You can specify aliases in the Alias column of the Selected Tables list. If you are creating a SELECT or SELECT DISTINCT statement, you can repeat this step to add more tables or views.

  5. On the Columns page, expand the tables or relational views in the Available Columns list to see the columns.

  6. Select one or more columns, and then click the > button. You can specify column aliases in the Alias column of the Selected Columns box.

  7. To remove a table, relational view, or column at any time, switch to the Tables or Columns page, select the table or column that you want to remove from the Selected Tables or Selected Columns list and then click the < button.

After your tables, relational views, and columns are selected, you can continue to build your statement by completing the fields on the other tabs in the wizard.

 

Parent topic

Editing an SQL statement