Click here to Skip to main content
15,900,906 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My database table has the following columns:
A1, A2, A3, A4, A5

I have to update these records and shift the values left whenever I come across a null column. The purpose is not to have null values between the columns that have a value starting from left. For example if:

A1 = NULL , A2 = 1 , A3 = 4, A4 = 5, A5 = 9

I have to shift the values left so the result will be:

A1 = 1, A2 = 4 , A3 = 5, A4 = 9, A5 = NULL


So far I have come up with the following query but it is slow. Let me know if you can tweak the query to make it faster. One more thing, What if I do this in c#? Will it be faster if I loop through the Datarows there and update each row?

SQL
UPDATE myTable SET
    A5 = A6,
    A6 = NULL
WHERE (A5 IS NULL) AND (NOT A6 IS NULL)

UPDATE myTable SET
    A4 = A5,
    A5 = A6
WHERE (A4 IS NULL) AND (NOT A5 IS NULL)

UPDATE myTable SET
    A3 = A4,
    A4 = A5,
    A5 = A6
WHERE (A3 IS NULL) AND (NOT A4 IS NULL)

UPDATE myTable SET
    A2 = A3,
    A3 = A4,
    A4 = A5,
    A5 = A6
WHERE (A2 IS NULL) AND (NOT A3 IS NULL)

UPDATE myTable SET
    A1 = A2,
    A2 = A3,
    A3 = A4,
    A4 = A5,
    A5 = A6
WHERE (A1 IS NULL) AND (NOT A2 IS NULL)
Posted
Comments
AspDotNetDev 22-Jun-11 19:08pm    
Have you considered using a cursor?
AspDotNetDev 23-Jun-11 14:56pm    
FYI, I don't get notified of your reply unless you click "reply" to my comment rather than clicking "add comment". What do you mean "how can I implement it in this query"? Do you want me to use use only update queries? Or would a cursor be acceptable? Do you need help understanding how a cursor would be implemented?
AspDotNetDev 23-Jun-11 15:29pm    
Perhaps. Might be able to get it to perform faster, but the query would be very long. My recommendation would be to go with a cursor unless you have a good reason not to.
AspDotNetDev 23-Jun-11 19:34pm    
OK, I added a solution that shows how to do it with a cursor.

Try something like this:
SQL
DECLARE @test table (ID int, A1 int, A2 int, A3 int)
INSERT INTO @test VALUES(1, 1, NULL, 3)
INSERT INTO @test VALUES(2, 1, 2, 3)
INSERT INTO @test VALUES(3, NULL,2 , NULL)
DECLARE @temp table (id int, val int)
DECLARE @id int
DECLARE eachRow CURSOR FOR
	SELECT ID, A1, A2, A3 FROM @test WHERE A1 IS NULL OR A2 IS NULL OR A3 IS NULL
DECLARE @rowID int, @A1 int, @A2 int, @A3 int
OPEN eachRow
WHILE 0 = 0
BEGIN
	FETCH NEXT FROM eachRow INTO @rowID, @A1, @A2, @A3
	IF @@FETCH_STATUS != 0 BEGIN BREAK END
	
	DELETE FROM @temp
	SET @id = 1
	IF @A1 IS NOT NULL
	BEGIN
		INSERT INTO @temp VALUES(@id, @A1)
	END
	SELECT @id = ISNULL((SELECT MAX(id) + 1 FROM @temp), 1)	
	IF @A2 IS NOT NULL
	BEGIN
		INSERT INTO @temp VALUES(@id, @A2)
	END
	SELECT @id = ISNULL((SELECT MAX(id) + 1 FROM @temp), 1)
	IF @A3 IS NOT NULL
	BEGIN
		INSERT INTO @temp VALUES(@id, @A3)
	END
	SELECT @id = ISNULL((SELECT MAX(id) + 1 FROM @temp), 1)
	SELECT @A1 = NULL, @A2 = NULL, @A3 = NULL
	SELECT @A1 = val FROM @temp WHERE id = 1
	SELECT @A2 = val FROM @temp WHERE id = 2
	SELECT @A3 = val FROM @temp WHERE id = 3
	UPDATE @test SET
		A1 = @A1, A2 = @A2, A3 = @A3
	WHERE
		ID = @rowID
END
CLOSE eachRow
DEALLOCATE eachRow
SELECT * FROM @test


I created a table variable to test with (I only used A1 through A3, but you can extend it to be through A6). You will want to ensure your table has an ID field with an index on it. Also, you may want to add an index to the temporary table variable I used in my example if the number of fields you have is large. If you actually have 6 rows, then you shouldn't need an index on the temporary table variable.
 
Share this answer
 
SQL
DECLARE @tmpTable as Table(A1 INT, A2 INT, A3 INT, A4 INT, A5 INT)
INSERT INTO @tmpTable(A1,A2,A3,A4,A5) VALUES (NULL,1,4,5,9)

SELECT * FROM @tmpTable

A1          A2          A3          A4          A5
----------- ----------- ----------- ----------- -----------
NULL        1           4           5           9


UPDATE @tmpTable SET A1 = A2, A2=A3, A3 =A4,A4 =A5, A5=A1

SELECT * FROM @tmpTable
A1          A2          A3          A4          A5
----------- ----------- ----------- ----------- -----------
1           4           5           9           NULL
 
Share this answer
 
v2
Comments
Mastersev 23-Jun-11 10:55am    
what if in the second record A3 is null, this wont work.
AspDotNetDev 23-Jun-11 14:57pm    
I'm not sure you understand the OP's problem, as this will not work as you have presented it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900