CODEWITHSUNDEEP
c#sqlsql-serverdatabaselist
Niek Jonkman
Niek Jonkman

Reputation: 497

UPDATE every record in SQL database from list

Hello I have a simple question that regards updating data into a MS MySql Database 2012 table. The table that I have is called COMPLETED and has 3 fields. student_ID (int, NOT allowed nulls) completed (bool, NOT allowed nulls) random_code (string, allowed nulls)

In c# I have a list filled with unique random codes. I want all codes updated into the database, so if I have 20 records I want 20 unique codes updated into the random_code field. So the first records gets the first code, the seconds records gets the second code and so on. I think the best way to do this is using a foreach and, for each code in the list of codes update that code into the random_code field in my database. 

    foreach (string unRaCo in codes)
    {
        //insert database
        SqlCommand toDB = new SqlCommand("UPDATE COMPLETED SET random_code = '"+ unRaCo +"'  ", conn);
        SqlDataReader toDBR;
        toDBR = toDB.ExecuteReader();
        toDBR.Close();
    }

The problem I have is that the update query updates ALL records with the first code, so the first record has the code 12345 for example but all other records also have that code. I want to update 12345 into record 1 and 54321 for example in number 2, how do I do that?

Upvotes: 0

Views: 2624

Answers (3)

objecto
objecto

Reputation: 54

I would recommend using a Hashtable with studentID as the Key and the code as the value. For each entry (student) in the Hashtable would be a unique code.

Then you could just do:

 UPDATE COMPLETED SET random_code = (the code provided by hashtable) WHERE student_id = (Hashtable KEY)

Upvotes: 2

Colm Prunty
Colm Prunty

Reputation: 1620

Well, there seems to be an accepted answer now. Nonetheless, this should do it with only the codes loop. 

var count = 0;
foreach (string unRaCo in codes)
{
    //insert database
    SqlCommand toDB = new SqlCommand(@"UPDATE c SET c.random_code = '" + unRaCo +"'
    FROM COMPLETED c
    JOIN (SELECT ROW_NUMBER() OVER (ORDER BY student_ID) AS number,
    Student_ID FROM COMPLETED) num ON num.student_ID = c.student_id
    WHERE num.number = " + count, conn);
    SqlDataReader toDBR;
    toDBR = toDB.ExecuteReader();
    toDBR.Close();
    count++;
}

Upvotes: 0

Stefano Bafaro
Stefano Bafaro

Reputation: 913

Your UPDATE query is missing the WHERE keyword, and you have to use the StudentID in it.

Something like this:

"UPDATE COMPLETED SET random_code = '"+ unRaCo +"'  "+"WHERE student_ID ="+ _studentidvariable_

Upvotes: 2

Related Questions