UPDATE every record in SQL database from list

Question

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?

Sorry, could you clarify - is this MySQL or Microsoft SQL Server 2012? — user1945782
– user1945782, Commented Oct 31, 2013 at 10:58
Wasn't this solved in your previous question? stackoverflow.com/questions/19704150/… — Ric
– Ric, Commented Oct 31, 2013 at 11:03
@Westie: Why did you delete your answer? I think it is the correct answer. — Indi
– Indi, Commented Oct 31, 2013 at 11:07

Suraj Singh · Accepted Answer · 2013-10-31 11:13:13Z

2

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_

edited Oct 31, 2013 at 11:13

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

answered Oct 31, 2013 at 11:09

Stefano Bafaro

91310 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suraj Singh · Accepted Answer · 2013-11-15 08:44:31Z

2

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)

edited Nov 15, 2013 at 8:44

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

answered Oct 31, 2013 at 11:10

objecto

543 bronze badges

Comments

Colm Prunty · Accepted Answer · 2013-10-31 11:27:05Z

0

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++;
}

answered Oct 31, 2013 at 11:27

Colm Prunty

1,6401 gold badge14 silver badges30 bronze badges

Collectives™ on Stack Overflow

UPDATE every record in SQL database from list

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related