CODEWITHSUNDEEP
sql-servert-sql
Malick
Malick

Reputation: 6732

How to merge two rows by updating only NULL values in TSQL?

I would like to merge two rows of data, by keeping a row based on its ID and only updating data if it is a NULL value.

As an example I want to "merge" row 1 and 2 and delete row 2:

From :

ID          date          col1          col2          col3       
--------------------------------------------------------------- 
1          31/12/2017       1           NULL          1
2          31/12/2015       3           2             NULL            
3          31/12/2014       4           5             NULL

To:

ID          date          col1          col2          col3       
--------------------------------------------------------------- 
1          31/12/2017       1           2             1
3          31/12/2014       4           5             NULL

In the example I want to keep row 1, and fill NULL values in row 1 by values that are in row 2. Then I will delete row 2. See below the code I have made for the date column.

    UPDATE MyTable
SET
   date = newdata.date
   FROM
    (
    SELECT
       date
    FROM  MyTable 
        WHERE
            ID =  2     
    ) 
    newdata
WHERE
     ID =  1 AND   MyTable.date IS NULL ;

I would like to perform the same operation on very large tables so I'm looking for a way to apply the above operation automatically (or a better workaround?) to every column of a table for two specific rows. To be clear, the column name (date) shouldn't be hardcoded as in the above example as I have plenty of different tables.

The table has many rows but I only want to merge two rows (this will always be two rows)

Could you help me with this ?

Upvotes: 0

Views: 508

Answers (1)

Thom A
Thom A

Reputation: 95554

I'm posting this an an answer now, as the comments from the OP do seem to infer this really is as simple as I thought it wasn't. Although their table has a lot of rows, they are only interested in correcting/merging the values of row 1 and 2. As these rows are simplistic then you can simply UPDATE the value of ID 1, and then DELETE row 2.

As there's only a few columns, then you could simply use literal values, as we can visually see that only Col2 on ID 1 needs to be updated:

UPDATE YourTable
SET col2 = 2
WHERE ID = 1;

Now ID 1 has the correct value, you can DELETE ID 2:

DELETE
FROM YourTable
WHERE ID = 2;

You could, however, do the following, if you're data is (a little) over simplified.

UPDATE YT1
SET Col1 = ISNULL(YT1.Col1,YT2.Col1),
    Col2 = ISNULL(YT1.Col2,YT2.Col2),
    Col3 = ISNULL(YT1.Col3,YT2.Col3),
    ...
FROM YourTable YT1
     JOIN YourTable YT2 ON YT2.ID = 2
WHERE YT1.ID = 1;

DELETE
FROM YourTable
WHERE ID = 2;

This is based on all the comments under the OP's question, that give some more (but not enough) detail. This is a dynamic SQL solution that is scalable, as it writes out the ISNULL expressions for the OP. Of course, if this doesn't help then once again I have the suggest they update their post to actually help us help them. Anyway, this should be self explanatory:

CREATE TABLE YourTable (ID int,
                        [date] date,
                        col1 int,
                        col2 int,
                        col3 int,
                        col4 int,
                        col5 int);

GO

INSERT INTO YourTable
VALUES (1,'20171231',1,NULL,1   ,2   ,NULL),
       (2,'20151231',3,2   ,NULL,NULL,4),            
       (3,'20141231',4,5   ,NULL,2   ,7);

SELECT *
FROM YourTable;

GO

DECLARE @SQL nvarchar(MAX);
DECLARE @TableName sysname = N'YourTable'
DECLARE @CopyToId int = 1;
DECLARE @DeleteID int = 2;

SET @SQL = N'UPDATE YT1' + NCHAR(10) +
           N'SET ' + STUFF((SELECT N',' + NCHAR(10) +
                                   N'    ' + QUOTENAME(c.[name]) + N' = ISNULL(YT1.' + QUOTENAME(c.[name]) + N',YT2.' + QUOTENAME(c.[name]) + N')'
                            FROM sys.tables t
                                 JOIN sys.columns c ON t.[object_id] = c.[object_id]
                            WHERE t.[name] = @TableName
                              AND c.name NOT IN (N'ID',N'date')
                            FOR XML PATH(N'')),1,6,N'') + NCHAR(10) +
          N'FROM ' + QUOTENAME(@TableName) + N' YT1' + NCHAR(10) +
          N'     JOIN ' + QUOTENAME(@TableName) + N' YT2 ON YT2.ID = @dDeleteID' + NCHAR(10) +
          N'WHERE YT1.ID = @dCopyToId;' + NCHAR(10) + NCHAR(10) +
          N'DELETE' + NCHAR(10) +
          N'FROM ' + QUOTENAME(@TableName) + NCHAR(10) +
          N'WHERE ID = @dDeleteID;';

PRINT @SQL; --Your Best friend

EXEC sp_executesql @SQL, N'@dCopyToID int, @dDeleteID int', @dCopyToId = @CopyToId, @dDeleteID = @DeleteID;

GO
SELECT *
FROM YourTable;
GO
DROP TABLE YourTable;

Upvotes: 3

Related Questions