August 30, 2008 05:21am pdt

Member Login

Hall of Fame

Yearly

1angelIII 852,060
2ee_rlee 458,453
3capricorn1 353,096
4chapmandew 340,052
5mark_wills 327,230
6aneeshatti... 189,721
7matthews... 187,246
8Thomasian 170,076
9jinesh_ka... 167,181
10sdstuber 147,877
11jimhorn 129,200
12peter57r 119,405
13harfang 111,183
14frankytee 108,644
15DatabaseMX 105,997
16hielo 94,350
17TextReport 84,195
18GRayL 82,934
19boag2000 81,858
20cactus_d... 77,446
21ScottPletc... 77,436
22dqmq 70,815
23LSMCons... 66,900
24mwvisa1 63,100
25dtodd 62,424

	[x] Attachment Details

How do I run this update without a cursor?

Zones: SQL Query Syntax, MS SQL Server

Tags: Microsoft, SQL Server, 2000

Hello experts, I have three tables in three different databases on the same server, laid out roughly as below. I need to update the TransDate in Table 3 to equal the TransDate in Table 1, and they're only linked by Table 2 which lists the cross reference IDs for both without foreign keys. Again, they're in different databases. Normally, I would create a cursor on Table 2 and iterate through each ID2, updating the dates one at a time; however, I'm trying to approach DBA respectability and work more with set-based queries. Is there a way to do this in one fell swoop?

This is a one-time update for a data conversion project, not an oft-used production function, so I can easily fall back on the ol' cursor if necessary. Speed isn't really a problem since there are only about 1,200 rows, but I have enough experience to hear alarm bells with the phrase "one-time update".

         
             Table 1    Table 2    Table 3
ID1        ID1        ID2
TransDate  ID2        TransDate

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.6

Question Stats

Zone: Programming

Question Asked By: grendel777

Solution Provided By: chapmandew

Participating Experts: 1

Solution Grade: A

Translate:

Loading Advertisement...

20080723-EE-VQP-34 / EE_QW_2_20070628