2

My database table has the following columns: A1, A2, A3, A4, A5, A6

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, A6 = 8

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

A1 = 1, A2 = 4 , A3 = 5, A4 = 9, A5 = 8, A6 = 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?

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)
Improve this question
3
  • 3
    Any reason why you're not just adding these on as a related table? You're violating normal forms by doing it this way... en.wikipedia.org/wiki/… Commented Jun 23, 2011 at 23:18
  • There are other columns too in this table and I am not able to change the design. I will have to find a way to do this without changing the design. it's an old system with poor Data model. Commented Jun 23, 2011 at 23:21
  • What if you declared variables for each column and then use IF statements. That should get you down to one SELECT and one UPDATE. I am unsure if the compiler is performing 5 SELECTs and then 1 UPDATE with your current code. Commented Jun 23, 2011 at 23:33

4 Answers 4

Reset to default
2

Given that [anything] + NULL + [anything] is null how about;

declare @t table(A1 int, A2 int, A3 int, A4 int, A5 int, A6 int) insert @t values (NULL, 2 , 3, 4, 5, 6), (1, NULL, 3, 4, 5, 6), (1, 2, NULL, 4, 5, 6), (1, 2, 3, NULL, 5, 6), (1, 2, 3, 4, NULL, 6), (1, 2, 3, 4, 5, NULL), (1, 2, 3, 4, 5, 6) update @t set A1 = coalesce(A1, A2), A2 = case when A1 + A2 is null then A3 else A2 end, A3 = case when A1 + A2 + A3 is null then A4 else A3 end, A4 = case when A1 + A2 + A3 + A4 is null then A5 else A4 end, A5 = case when A1 + A2 + A3 + A4 + A5 is null then A6 else A5 end, A6 = case when A1 + A2 + A3 + A4 + A5 is null then null else A6 end from @t select * from @t A1 A2 A3 A4 A5 A6 2 3 4 5 6 NULL 1 3 4 5 6 NULL 1 2 4 5 6 NULL 1 2 3 5 6 NULL 1 2 3 4 6 NULL 1 2 3 4 5 NULL 1 2 3 4 5 6
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

And what happens if both A1 and A2 are NULL ?
2

How about using coalesce in a statement something like this:

update mytable set a1 = coalesce(a1,a2,a3,a4,a5,a6), a2=coalesce(a2,a3,a4,a5,a6), a3=coalesce(a3,a4,a5,a6), a4=coalesce(a4,a5,a6), a5=coalesce(a5,a6)
Improve this answer

2 Comments

What do you mean it won't work because it copies the values? This wasn't meant to be the complete solution, just sample approach. You could add WHERE's and/or break it up into several more specialized queries but I think coalesce will work.
Let me try it today, I will let you know of the results. So far the most useful answer is yours
0

If you have no control over the code or the database design, I would recommend solving this problem using a trigger.

Set up an INSERT, UPDATE trigger that will specifically only look at the rows that have just been changed. That way you're not running an update statement on data that has already been checked for consistency. Less rows to check/update means better performance.

You can narrow it down to the rows that have been touched by looking at the virtual inserted and deleted tables.

Once your trigger is in place either touch all of the existing rows (to fire the trigger on them) or run your original script to make sure that everything is in a consistent state.

Improve this answer

Comments

0

If not that, have you tried adding covering indexes on all of your columns? Try adding indexes in the following way. This is so that it can find the rows that need updating faster, the covering index is so that it doesn't have to do a bookmark lookup to get the next value it needs.

A1, A2, A3, A4, A5, A6 A2, A3, A4, A5, A6 A3, A4, A5, A6 A4, A5, A6 A5, A6
Improve this answer

2 Comments

I don't know how to add the covering index.
Wouldn't adding new indexes make the update SLOWER since those would also need to be updated in each statement? Accessing the rows more quickly is nice, but indexes have an increased update cost as well.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.