I have a table with the following data . Some rows have col2 as null
col1 col2 col3
1 203 1232
2 null 2343
3 502 15566
4 null 4566
5 null 4545
6 601 4545
7 601 121663
I need the col2 null records to be updated with non null values that exist right before it
The result should look like this
col1 col2 col3
1 203 1232
2 203 2343
3 502 15566
4 502 4566
5 502 4545
6 601 4545
7 601 121663
Thank you !
August 28, 2019 at 9:18 pm
SQL Tables represent sets which are UNORDERED. There is no concept of "values that exist right before it", because there is no concept of "before". You have to specify some expression(s) to order by before we can begin to answer that question. Presumably, you want to order by col1, since that seems to match the order that you want.
The simple way assumes that you only ever have one consecutive record with a null value. If you can have multiple consecutive records with null values, you'll need to do something a bit more complex and the exact process depends on the data types and expected ranges of your fields (which you've failed to provide).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 28, 2019 at 9:24 pm
August 28, 2019 at 9:40 pm
Well, I would help but you haven't provided the DDL (CREATE TABLE) script(s) needed, nor the necessary INSERT statements to populate the table(s) with sample data. If we had those it wouldn't take long to work out a solution, but since I am at work I don't have the time to do that work for you. Help us help you, post the DDL and DML scripts to create a test environment to work with. Remember, we are volunteers, not paid employees.
August 28, 2019 at 10:17 pm
This is the approach. You can use CHAR instead of BINARY, but then you have to worry about padding the strings so that the MAX() works correctly. You can also use a similar method where you create a BIGINT out of the two INT fields. I find this approach the simplest and the most readily adjustable to varying data types and sizes (although you do have to be careful, because some data types are little endian (e.g., DATE) and some are big endian (e.g., DATETIME) and this only works with big endian data types).
SELECT
col1,
CAST(SUBSTRING(MAX(CAST(col1 AS BINARY(4)) + CAST(col2 AS BINARY(4))) OVER(ORDER BY col1 ROWS UNBOUNDED PRECEDING), 5, 4) AS INT) AS col2,
col3
FROM YourTable
Drew
edited to correct the big endian and little endian data types.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 28, 2019 at 11:08 pm
This looks like it works...
CREATE TABLE #Test(
col1 int,
col2 int,
col3 int);
GO
INSERT INTO #Test VALUES
(1, 203, 1232)
,(2, null, 2343)
,(3, 502, 15566)
,(4, null, 4566)
,(5, null, 4545)
,(6, 601, 4545)
,(7, 601, 121663);
Solution?
SELECT col1
, col2
, col3
, (SELECT MAX(col2) FROM #Test t2 WHERE t2.col1<=t1.col1) As Prev
FROM #Test t1
ORDER BY col1;
Create the table:
IF OBJECT_ID('tempdb..#myTable','U') IS NOT NULL
DROP TABLE #myTable
SELECT *
INTO #myTable
FROM (VALUES (1, 203, 1232),
(2, null, 2343),
(3, 502, 15566),
(4, null, 4566),
(5, null, 4545),
(6, 601, 4545),
(7, 601, 121663)) T(col1,col2,col3)This method loops round updating the rows that have a null value in the current row and a not-null in the previous row until all rows have been updated.
SELECT * FROM #myTable
DECLARE @RowCount int=-1
WHILE @RowCount <> 0 BEGIN
;WITH CTE AS
(
SELECT t.col1, t.col2, LAG(t.col2) OVER (ORDER BY t.col1) Col2New
FROM #myTable t
)
UPDATE CTE
SET CTE.col2=CTE.Col2New
WHERE CTE.col2 IS NULL
AND CTE.Col2New IS NOT NULL
SET @RowCount = @@ROWCOUNT
END
SELECT * FROM #myTable
This is drew allen's query turned into an update (no loop needed)
SELECT * FROM #myTable
;WITH CTE AS
(
SELECT t.col1,
t.col2,
CAST(SUBSTRING(MAX(CAST(t.col1 AS BINARY(4)) + CAST(t.col2 AS BINARY(4))) OVER(ORDER BY t.col1 ROWS UNBOUNDED PRECEDING), 5, 4) AS INT) AS col2New
FROM #myTable t
)
UPDATE CTE
SET CTE.col2 = CTE.col2New
WHERE CTE.col2 IS NULL
SELECT * FROM #myTable
August 29, 2019 at 2:08 am
I have a table with the following data . Some rows have col2 as null
col1 col2 col3
1 203 1232
2 null 2343
3 502 15566
4 null 4566
5 null 4545
6 601 4545
7 601 121663
I need the col2 null records to be updated with non null values that exist right before it
The result should look like this
col1 col2 col3
1 203 1232
2 203 2343
3 502 15566
4 502 4566
5 502 4545
6 601 4545
7 601 121663
Thank you !
Does Col1 have a UNIQUE Clustered Index on it? If not, can it? If so, this problem will be incredibly simple.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2019 at 3:32 pm
This looks like it works...
CREATE TABLE #Test(
col1 int,
col2 int,
col3 int);
GO
INSERT INTO #Test VALUES
(1, 203, 1232)
,(2, null, 2343)
,(3, 502, 15566)
,(4, null, 4566)
,(5, null, 4545)
,(6, 601, 4545)
,(7, 601, 121663);Solution?
SELECT col1
, col2
, col3
, (SELECT MAX(col2) FROM #Test t2 WHERE t2.col1<=t1.col1) As Prev
FROM #Test t1
ORDER BY col1;
This solution only works if col2 is monotonically increasing(decreasing) with respect to col1. While this is true of the sample data, there is no reason to assume that this must be the case and if it is true then the following will perform better.
WITH T AS
(
SELECT
t.col1
,t.col2
,t.col3
,MAX(t.col2) OVER(ORDER BY t.col1 ROWS UNBOUNDED PRECEDING) AS new_col2
FROM #Test AS t
)
UPDATE T
SET col2 = T.new_col2
WHERE T.col2 IS NULL;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 30, 2019 at 4:19 am
It would appear that the OP has flown the coop instead of answering the question I posed.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply