Insert into first null row

Question

I am trying to add the value at first null column.This is my code-

SqlCommand cmd = my_con.CreateCommand();
                    cmd.CommandText = "insert into Contents(General_Exam) values(@CC)";
                    cmd.Parameters.AddWithValue("@CC", txtAddOption.Text);
                    cmd.ExecuteNonQuery();
                    comboBoxAddOption.Items.Add(txtAddOption.Text);
                    SqlCommand cmd1 = new SqlCommand("update TOP(1) Contents SET General_Exam=@CC where General_Exam IS NULL", my_con);
                    cmd1.Parameters.AddWithValue("@CC", txtAddOption.Text);
                    cmd1.ExecuteNonQuery();

But when I am inserting value it not inserting in first null row instead of that it get inserted into last null row.I think this happen because Top(1) taking top row.But I want to insert into first null row any one have idea how I able to do that Please tell! Thanks in Advanced.

What RDBMS are you using? http://stackoverflow.com/help/how-to-ask — Tom H, Feb 24 '16 at 15:25
Define "first" and "last". There is no guaranteed implicit order of rows in SQL Server. You need to explicitly identify the record you want to update, possibly with an explicit `ORDER BY` in this case. — David, Feb 24 '16 at 15:25
To expand on Davids Comment. You need something else to look at order of the record. It is possible to use a date of last edit or entry. You could also look at the use of an identity column. if you are looking for the "first" or oldest record this should make this a good deal easier. Otherwise as previously stated you will get varied results since the ordering is not explicit. — AhDev, Feb 24 '16 at 15:31
Yes I think about ORDER BY but I don't understand ORDER BY what I have to do? — Rock, Feb 24 '16 at 15:31
If I done order by Date or Id I don't think it will help to insert into the first null row of my table:( — Rock, Feb 24 '16 at 15:33

score 1 · Answer 1 · answered Feb 24 '16 at 15:27

1

Using UPDATE TOP 1 is almost certainly a mistake. You have a problem with your design if you need to do something like that. Also of importance to note - TOP x without an ORDER BY clause should never happen. If you find yourself doing that then you should look back to see where you've made a mistake in your logic and/or in your design.

Your table should have a primary key and you should be updating based on that, not based on some (at the moment) ordering of the data.

answered Feb 24 '16 at 15:27

Tom H

46,766
14
87
128

Particularly as the order in which data is accesed is NOT DETERMINED in any sql server. So the first row CAN and WILL change between calls, particularly on a more powerfull machine with multiple users. – TomTom Feb 24 '16 at 15:31
@Tom H You are right I need to add the primary key to some column But This is just content table and every column content is independent of each other so If I add primary key it will be Make them dependent and it will create more problem:( – Rock Feb 24 '16 at 15:47
@Rock Actually no. The primary key can be as simple as an ID column that allows identification of a row. A table pretty much MUST have a primary key - this is a core concept in SQL if you want reliable updates. – TomTom Feb 24 '16 at 15:52
This goes back to the important issue - the database is not well designed. I would strongly encourage you to read some introductory books on database design. Some of the concepts here are very fundamental to using a database efficiently and effectively. – Tom H Feb 24 '16 at 15:53
Yes you both are right adding Primary key is really important factor in sql but In my case if I add Primary Key I don't think it will help me in any way because this all are independent component.About design I think you are right but what design I have to refer I don't understand:( – Rock Feb 24 '16 at 16:14

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

This thread talks about using common table expressions to control the order by in an update using top:

how can I Update top 100 records in sql server

also discussed here:

SQL UPDATE TOP with ORDER BY?

;WITH CTE AS 
( 
SELECT TOP 1 * 
FROM Contents 
WHERE General_Exam IS NULL
ORDER BY [Chife Complains] DESC
) 
UPDATE CTE SET General_Exam=@CC

Note: Hopefully there's something better you can sort on with the ORDER BY. (Same sort you're using for the display likely)

edited May 23 '17 at 12:07

Community

1
1

answered Feb 24 '16 at 15:41

ebyrob

667
7
24

Your code Adding value in one null column again and again.But your code and your links given me a some idea for my query thanks for that. – Rock Feb 24 '16 at 16:54

Insert into first null row

2 Answers2