Its hard to explain my problem, so I added an image.
I have ReportedGMY collumn in tblother. I need to update values into ReportedGMY and I have to save in tblhistory old ReportedGMY value and new ReportedGMY value.
First, I executed INSERT INTO SELECT query and save to tblhistory ReportedGMY value from tblother. And I updated tblother and changed values. Then, I inserted to tblhistory again.
Now, I need to carry up OldValue values with a query. Then delete null rows. How can do this? Or another way ?
INSERT INTO tblhistory(FindingID,NewValue,[Date])
SELECT ID, ReportedGMY, ,GETDATE() AS CurrentDateTime
ID, tblother.ReportedGMY ,
REPLACE(tblother.ReportedGMY, ' ', '') AS NewNumbers
tblother INNER JOIN tblother2 ON tblother.ReportID = tblother2.ID
WHERE A.ReportedGMY like '%,Y,%' OR A.ReportedGMY like 'Y,%' OR A.ReportedGMY like '%,Y'
I've used a subquery here to return just the
IS NOT NULL. These values are then used to update the rows that have
UPDATE a SET a.OldValue = b.OldValue FROM TableName a INNER JOIN (SELECT FindingID, OldValue FROM TableName where OldValue IS NOT NULL) b ON a.FindingID = b.FindingID WHERE a.OldValue IS NULL
You can then get rid of data with a
null in ChangeArea like this
DELETE FROM TableName WHERE ChangeArea IS NULL
Another option would be to extract all data into a temp table, truncate your table, then re-insert the data. Something like this (Assuming that your ID field is an
IDENTITY field as it appears)
SELECT FindingID ,MAX(ChangeArea) ChangeArea ,MAX(OldValue) OldValue ,MAX(NewValue) NewValue ,MAX(Date) Date INTO #TempTable FROM TableName GROUP BY FindingID TRUNCATE TABLE TableName INSERT INTO TableName (FindingID, ChangeArea, OldValue, NewValue, Date) SELECT FindingID ,ChangeArea ,OldValue ,NewValue ,Date FROM #TempTable DROP TABLE #TempTable
The advantage of this would be that you'll definitely only have one row per FindingID. The disadvantage is that if your table is large then you're going to be hitting tempdb quite hard and it may take a while to process.
Edit: To change that second statement to an update you'll want something like this;
UPDATE a SET a.ChangeArea = b.ReportedGMY ,a.NewValue = REPLACE(b.ReportedGMY, ' ','') ,a.Date = GETDATE() FROM tblhistory a INNER JOIN tblother b ON a.FindingID = b.FindingID WHERE b.ReportedGMY like '%,Y,%' OR b.ReportedGMY like 'Y,%' OR b.ReportedGMY like '%,Y'