CODEWITHSUNDEEP
c#sql-serverasp.net-mvc
Ale
Ale

Reputation: 41

Update specifc rows using SQL

I need to read through all the records in a table and for each record, if a certain condition is met, set a flag on that record with a true or false value. What happens is that my method inserts true for every record.

How do I make my method insert true or false depending on the condition?

For example, if I have a value of 11 in the Code column then insert a true into the Flag column for that record, but if the Code column has a value of 7 then insert a false instead.

Query:

string select = "SELECT * FROM Movies";

Method to read the records:

using (SqlDataReader sdr = cmd.ExecuteReader())
{
    while (sdr.Read())
    {

        if (string.IsNullOrEmpty(sdr["Code"].ToString()))
        {
            C_E = "0";
            this.Update(C_E);
            //ViewBag.Message = "cero";
        }
        else
        {
            C_E = sdr["Code"].ToString();
            this.Update(C_E);
            // ViewBag.Message = C_E;
        }

    }
}

Method to update the table:

private string Update(string C_E)
{

    ReglaController re = new ReglaController();

    string query = "UPDATE Movies SET Flag = @code";

    string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constr))
    {

        SqlCommand command = new SqlCommand(query, con);
        //command.Parameters.Add("@code", SqlDbType.NChar).Value = ("o");
        command.Parameters.Add("@code", SqlDbType.NChar).Value = re.Condition(C_E);

        con.Open();
        command.ExecuteNonQuery();

    }

    return C_E;
}

Method to test the condition: 

public string Regla_4(string C_E)
{
    string query = "SELECT Emp.Code FROM Empresas WHERE Emp.Code = @codigo";
    string R2 = "l";

    string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constr))
    {
        SqlCommand command = new SqlCommand(query, con);
        command.Parameters.Add("@codigo", SqlDbType.Int).Value = C_E;

        con.Open();
        command.ExecuteNonQuery();

        using (SqlDataReader sdr = command.ExecuteReader())
        {

            if (sdr.HasRows)
            {
                R2 = "True";
            }
            else
            {
                R2 = "False";
            }
        }

    }
}

Upvotes: 1

Views: 215

Answers (2)

Andy Hames
Andy Hames

Reputation: 681

The problem is that your update query does not tell the database which row to update, so it updates the entire table. You need to tell it which row to update by including a WHERE clause.

Upvotes: 5

Suraj Kumar
Suraj Kumar

Reputation: 5653

You need to write your update statement as shown below

string query = "UPDATE Movies SET Flag = CASE WHEN Code = 11 then true case when Code = 7 then false end";

Hope this will help you

Upvotes: 0

Related Questions