Search code examples
sqlsql-serverinsert-update

How to Join Rows SQL?

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

tblhistory:

tblhistory

I have ReportedGMY column 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?

Solution

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