How to simplify update query in mysql?

I am doing a college attendance project (Winforms, Mysql, C#)

I want to update 0 to P, 1 to Ab and 2 to OD in all period columns. For that I write a for loop and three update query.

Is it any possible idea for do this in one single query?

Please help me...

 for (int i = 1; i <= 8; i++)
            {
                string period = 开发者_运维技巧"Period" + i;
                string t1p = period1;

                command.CommandText = "update attendance_daily_rpt set " + t1p + " = 'P' where " + t1p + " = 0";
                connection.Open();
                command.ExecuteNonQuery();                
                connection.Close();

                command.CommandText = "update attendance_daily_rpt set " + t1p + " = 'Ab' where " + t1p + " = 1";
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();

                command.CommandText = "update attendance_daily_rpt set " + t1p + " = 'OD' where " + t1p + " = 2";
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }

Thanks in advance...

Create a table for maintaining the mapping (attendance_daily_rpt_map):

+-----------+---------+
| FromValue | ToValue |
+-----------+---------+
| 0         | P       |
| 1         | Ab      |
| 2         | OD      |
+-----------+---------+

And then change your query:

UPDATE
    attendance_daily_rpt
    INNER JOIN attendance_daily_rpt_map ON attendance_daily_rpt.t1p = attendance_daily_rpt_map.FromValue
SET
    attendance_daily_rpt.t1p = attendance_daily_rpt_map.ToValue

SQL:

SET t1p = 
CASE t1p
 WHEN '1' THEN 'AB'
 WHEN '2' THEN 'OD' 
END

Make C# string yourself ;)

Possibly but it would most likely be a monstrosity that may very well perform worse than twenty-four separate targeted updates. I don't see anything particularly wrong with your current solution other than the fact that:

• you may want to do it as a transaction; and
• it may not be necessary to close and open the connection for every single SQL statement.

I'd try to start with something like:

connection.Open();
// Start transaction with whatever it takes.
for (int i = 1; i <= 8; i++) {
    string t1p = "Period" + i;;
    command.CommandText = "update attendance_daily_rpt set " + t1p
        + " = 'P' where " + t1p + " = '0'";
    command.ExecuteNonQuery();                
    command.CommandText = "update attendance_daily_rpt set " + t1p
        + " = 'Ab' where " + t1p + " = '1'";
    command.ExecuteNonQuery();
    command.CommandText = "update attendance_daily_rpt set " + t1p
        + " = 'OD' where " + t1p + " = '2'";
    command.ExecuteNonQuery();
}
// Commit transaction.
connection.Close();

You could also simply the individual statements down to an array lookup with something like:

string[] vals = new string[] {"P", "Ab", "OD"};
connection.Open();
// Start transaction with whatever it takes.
for (int i = 1; i <= 8; i++) {
    string t1p = "Period" + i;;
    for (int j = 0; j < vals.Length; j++) {
        command.CommandText = "update attendance_daily_rpt set " + t1p
            + " = '" + vals[j] +"' where " + t1p + " = '" + j + "'";
        command.ExecuteNonQuery();                
    }
}
// Commit transaction.
connection.Close();

You can use a CASE statement

UPDATE
    attendance_daily_rpt
SET
    PeriodX = CASE PeriodX
                 WHEN '0' THEN 'P'
                 WHEN '1' THEN 'Ab'
                 WHEN '2' THEN 'OD'
                 ELSE PeriodX
             END