ApexSQL Knowledgebase: Quickly Edit Queried Data Using Drill Down

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

Quickly Edit Queried Data Using Drill Down

DESCRIPTION
Using the new features of ApexSQL's Drill Down, the process of editing data from query results just got a lot easier.

SOLUTION
This solution will show how to use the the Drill Down feature to quickly edit data from queries.

Using the Northwind database, we search the row from the Order table with Customer ID = VINET, Shipped Date = 1996-07-16, and Unit Price = $9.80. We need to update this record's unit price to $10.50.

To begin, we run the following query:

select  Ord.OrderID, Ord.CustomerID, Ord.ShippedDate, Detail.UnitPrice
from    Orders Ord (NoLock)
       join [Order Details] Detail (NoLock)
          on Detail.OrderID = Ord.OrderID
where   Ord.CustomerID = 'VINET'

This gives us the following results:

By examining the rows shown, we see that the Shipped Date and Unit Price we are looking for is in the second row. At this point, we would need to start constructing an update statement. Instead of manually typing the update statement, we use ApexSQL's Drill Down. To do is, we simply find the column(s) that is/are unique to the table containing the record we want to update. In this example, the column is OrderID.

Right click on the row and select Drill Down from the context menu. This will launch the Drill Down - Multi-Column dialog. Select the column(s) you wish to use to perform the Drill Down on and click OK.

If only one column is needed for the Drill Down, you can bypass the Multi-Column dialog by double clicking on the column you want to use (in this case, OrderID).

The Drill Down window (shown below) lists the tables that have the columns that you are "drilling" on. As you click a table in the list on the left, column values from the row you started the Drill Down from will be listed out on the grid.

Click on the Order Details table and we will see in row 2 the Unit Price that we need to change. Click on the cell and type in the new value of $10.50.

After you change the value, scroll down to the changed row. Notice that the background of the cell that was just changed has changed.

This has not been committed to the database. Press the Update button to commit the change or the Refresh button to discard your changes. Once you press the Update button you are done. Click OK to close the window. Your record has been updated.

AUTHOR
Stephen Schmidt

LAST REVIEW DATE
27 August 2007

Labels: Apex SQL Edit

posted by Janice Lee on Friday, July 27, 2007

0 Comments: