October 31, 2006 at 10:36 am
Hi,
I made a mistake in writing one of my queries, and now data is missing from our production environment. I have two columns in a table in the backup database. The data from these two columns was to be merged and then carried over into a new field within the same table. The two columns were then deleted. My query was not robust and so now I have missing data.
I need to copy data from the Recommendation and Comments fields in the backup database to the new TechnicalSolution field in the live database, without overwriting any changes that users may have made to the TechnicalSolution since the time of the backup.
Does anybody know an easy way to do this?
Thx.
October 31, 2006 at 10:48 am
First of all, test all the queries in development before moving to production. Backup the tables in production before doing the update.
Does the table have modifytime field so that you know when the users made the changes?
If the table has a modify time field then just don't touch the record has modify time > than the time you updated the table, otherwise update the table with correct data.
Is the TechnicalSolution field = Recommendation + Comments?
When you said you had missing data, was that meant the technicalSolution did not get updated?
October 31, 2006 at 11:01 am
Hi,
The table does not have a timestamp field to indicate last modification date.
My original query was:
UPDATE RfsTrn
SET TechnicalSolution = Recommendation + ' ' + CAST(Comments AS varchar(8000))
Comments was a text field so I had to cast it. This query only worked where both Recommendation and Comments were both not NULL. If either was NULL, the query did not work. Hence my problem.
I want to do something like this:
UPDATE RfsTrn
SET TechnicalSolution =
(SELECT Recommendation
FROM jsirfs_20061028.dbo.RfsTrn
WHERE Recommendation IS NOT NULL AND Comments IS NULL)
+ ' ' + TechnicalSolution
And then run something similar for Comments only. This query won't run for obvious reasons.
I really don't want to have to copy data over manually for 1200+ records.
October 31, 2006 at 12:08 pm
So I assume you want to update when TechnicalSolution IS NULL.
UPDATE RfsTrn
SET TechnicalSolution =
CASE WHEN Recommendation IS NULL
THEN CASE WHEN Comments IS NULL
THEN NULL ELSE CAST(Comments AS VARCHAR(8000))
END
WHEN Recommendation IS NOT NULL
THEN CASE WHEN Comments IS NULL
THEN Recommendation
ELSE Recommendation + ' ' + CAST(Comments AS VARCHAR(8000)
END
ELSE TechnicalSolution END
WHERE TechnicalSolution IS NOT NULL
This way you don't have to update the table twice.
October 31, 2006 at 12:09 pm
Sorry
The statement should be
Update
SET
WHERE TechnicalSolution IS NULL
November 10, 2006 at 6:51 pm
Thanks for the help.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply