update column values which are null

Question

2.50/5 (2 votes)

See more:

I have one column in my sql database named as S-No.
Which has values like following :

S-No
____
1
____
2
____
Null
____
Null
____
3
____
Null
____
Null
____
Null
____
Null
____
Null
____
4
____
Null
.
.

Now, i want to write query in such a way that, which S-No column contains Null between 1 to 2, update there value to 1..... And which has Null between 2 To 3, update there value to 2... And so on... eg. 3 to 4 -- update as 3, 4 to 5 --- update as 4, 5 to 6...... I hope you will suggest me the query.. i am not able to write.. :( i hope you guyz will help.

Posted 19-Feb-15 2:49am

Harpreet_125

Add a Solution

Comments

Herman<T>.Instance 19-Feb-15 8:57am

is there an ID field besides [S-no]?

Harpreet_125 19-Feb-15 9:27am

This is not unique id column.. it can have duplicate values..

PIEBALDconsult 19-Feb-15 9:30am

He says there's "one column" so I read that as "there are no other columns".

Harpreet_125 19-Feb-15 9:57am

no, there are other columns also.

PIEBALDconsult 19-Feb-15 13:30pm

That's not what you said.

Herman<T>.Instance 19-Feb-15 11:13am

Then he got himself in troubles

PIEBALDconsult 19-Feb-15 9:02am

No, records in a database aren't in any particular order. You may get the rows in different orders each time you query them. You would have to provide some order, and in this case that seems unlikely.

Harpreet_125 19-Feb-15 9:27am

This is not unique id column.. it can have duplicate values..

PIEBALDconsult 19-Feb-15 9:29am

Obviously. Tell us something we don't know.

Wendelius 19-Feb-15 9:04am

As PIEBALDConsult pointed out the records are in no actual order. Is this really the structure of the database? can you post the Table definition, what columns it contains, what data types etc.

Harpreet_125 19-Feb-15 9:27am

This is not unique id column.. it can have duplicate values..

Wendelius 19-Feb-15 9:36am

Yes, I can see that based on your data. Again what is the actual table structure? Without knowing the table structure it's impossible to help you to write the statement.

ZurdoDev 19-Feb-15 9:11am

Only possible through sql if there is something to sort by to guarantee the order. Otherwise, export to excel, fit it, and import back in.

Harpreet_125 19-Feb-15 9:27am

This is not unique id column.. it can have duplicate values..

ZurdoDev 19-Feb-15 9:28am

I know. But if you want to do this in SQL you need to order by something as everyone is pointing out to you.

Harpreet_125 19-Feb-15 9:28am

can you please tell me how to fit in excel..

ZurdoDev 19-Feb-15 9:28am

Fix, not fit. Export to Excel. Sort it the way you want, then type in the values.

Kuthuparakkal 19-Feb-15 9:42am

Add an Identity Column (1,1) to your table, say ID
Now you order by ID check NULLS and update. I may post a solution later

1 solution

Add a Solution

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

Wendelius · Answer 1 · 2015-02-19T03:43:00

It really seems that the design is incorrect. The first thing you must understand is that the data is in no order. Another thing is that related data is stored in a single row (if it applies to single entity) or is joined by a reference when the data is spread to separate entities.

I think that the best advice at this point is to encourage you to get to know 3rd normal form (see http://en.wikipedia.org/wiki/Third_normal_form[^])

One easy way is to start from http://www.tutorialspoint.com/sql/sql-rdbms-concepts.htm[^] and have a look at database normalization. There's a tutorial which wil guide you through first, second, and third normal forms. In my opinion this is what you probably should use in your situation.

update column values which are null

1 solution

Solution 1

Add your solution here

Preview 0

update column values which are null

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us