pseudocode pseudocode - 5 months ago 5
SQL Question

How to Join Rows SQL?

Its hard to explain my problem, so I added an image.

tblhistory:
tblhistory

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(second) query:

INSERT INTO tblhistory(FindingID,NewValue,[Date])
SELECT ID, ReportedGMY, ,GETDATE() AS CurrentDateTime
FROM
(
SELECT
ID, tblother.ReportedGMY ,
REPLACE(tblother.ReportedGMY, ' ', '') AS NewNumbers
FROM
tblother INNER JOIN tblother2 ON tblother.ReportID = tblother2.ID
) A
WHERE A.ReportedGMY like '%,Y,%' OR A.ReportedGMY like 'Y,%' OR A.ReportedGMY like '%,Y'


If I can write this query as UPDATE, the problem will be fixed I think. What do you think?

Answer

I've used a subquery here to return just the FindingID and OldValue where OldValue IS NOT NULL. These values are then used to update the rows that have NULL in OldValue.

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'
Comments