0

Due to recent updates to the recent database, I have run into a weird problem. I have two tables, tVehicleDeal table and tVehicleLog table. We did a 'migration' meaning we created an app that will transfer the data from a old database to a more relational database. This process took awhile, but it finished and everything seemed good to go. What happens now, is that anytime tVehicleDeal is updated, the corresponding information is inserted into tVehicleLog. The problem that has occurred is.. I ran a script that would update the current deal in tVehicleDeal to the most recent log in tVehicleLog. I made an error in my script, and not all the current deals in tVehicleDeal were updated properly. As a result, when the users updated the active deal in tVehicleDeal, not all the information was inserted into the tVehicleLog. I need to find a way to update the newest entry with some fields from the past entries such as the date it was titled. Some Deals have as many as 20 different logs for it whereas some may have only 2 or 3. I have found this link here but I'm not 100 percent positive this is what I'm looking for. I have tried something similar to this but I am unable to get anything to work using the examples found on that page. Any other ideas will help greatly!

EDIT:

What I am unable to figure out is how to update a column in tVehicleLog. For example:

In the tVehicleLog table there are 6 results for a particular DealID.

The first through 4 do not have a titled date in it, but the 5th row does have a titled date.

I can't figure out how to update the titled column the 6th row for that dealID based on the 5th row that does have the titled date.

The link provided above looked like it was something I was looking for but I was unable to get that solution to work.

Community
  • 1
  • 1
Humpy
  • 2,004
  • 2
  • 22
  • 45
  • Any chance you can provide some details other than you need to update some table with some data from some data based on some criteria? Your question is so vague I can't even understand what the issue is. – Sean Lange Aug 04 '14 at 14:43
  • My bad, multi-tasking, probably lost my train of thought. I'll edit. – Humpy Aug 04 '14 at 14:45
  • The edit sort of helps but not a lot. How about a sqlfiddle? And an explanation of the desired results? – Sean Lange Aug 04 '14 at 14:53
  • The desired results is just to update the latest row/last inserted row with the titled date from one of the rows before it. – Humpy Aug 04 '14 at 14:58
  • Remember that a table is an unordered set. That means you have to define what "before" means. I can think of at least a dozen possibilities for this but since you aren't providing any details I can't provide a solution. – Sean Lange Aug 04 '14 at 15:07

1 Answers1

1

Based on this line from your question,

I can't figure out how to update the titled column the 6th row for that dealID based on the 5th row that does have the titled date.

It seems like this should fix your problem. It is written only to solve this specific scenario. If other scenarios exist that are not exactly like this one, adjustments may have to be made. If I didn't understand your problem, please post further clarification.

UPDATE L1
  SET TitleDate=L2.TitleDate
FROM tVehicleLog L1
INNER JOIN tVehicleLog L2
  ON L1.DealID=L2.DealID
  AND L2.TitleDate IS NOT NULL
WHERE L1.<PrimaryKeyColumn>=@ThePrimaryKeyColumnOfTheRowYouWantToUpdate
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • This almost worked! This is close to what I need, however it updated all the logs for that deal with the titled date including the ones that aren't suppose have a titled date. I will see if I can tweak it, but do you have any suggestions on how to accomplish this? – Humpy Aug 04 '14 at 15:08
  • You need to use something in the WHERE clause that UNIQUELY identifies the row you want to update. Something that will only be true for that one row. – Tab Alleman Aug 04 '14 at 15:10