如果查询有值，则更新表

本文关键字：更新 查询 如果 | 更新日期: 2023-09-27 18:06:06

我有一个查询，它给了我一个值，它低于

protected void CmdUpdate_Click(object sender, EventArgs e)
{
    string query = "select mkey from xxacl_pN_LEASES_ALL where project_id = '" + 
                        ddlProject.SelectedValue + 
                        "' and " +
                        "building_id = '" +
                        ddlBuilding.SelectedValue + 
                        "' and SALES_USER_ID = '" + 
                        ddlSalesUser.SelectedValue + 
                        "'";
}

现在我想要的是，如果查询有一些值，然后写update....语句，这是我知道的。如果不是，则不更新。

我想知道如何检查查询条件是否有值?

My update statement

Update xxacl_pn_leases_all set ASSIGNED_TO = '' where mkey = query

如果查询有值，则更新表

try

Update xxacl_pn_leases_all set ASSIGNED_TO = '' where your_all_condition_here

如

Update xxacl_pn_leases_all set ASSIGNED_TO = '' where project_id = '" + ddlProject.SelectedValue + "' and "+
                    "building_id = '" + ddlBuilding.SelectedValue + "' and SALES_USER_ID = '" + ddlSalesUser.SelectedValue + "'";

上面的查询将更新满足条件的行。

N。B:内联查询不适合实践，请尝试使用参数化查询。

您可以使用IF EXISTS.，也可以使用SQL Parameters:

string query = "IF EXISTS (SELECT mkey from xxacl_pN_LEASES_ALL where project_id = @ID AND building_id =@BUILD_ID and SALES_USER_ID =@User_ID) UPDATE xxacl_pN_LEASES_ALL SET .....";
SqlConnection con = new SqlConnection(ConStr);
con.Open();
SqlCommnad cmd = new SqlCommand(query, con);
cmd.Paramaters.AddWithValue("@ID", ddlProject.SelectedValue);
cmd.Paramaters.AddWithValue("@BUILID",ddlBuilding.SelectedValu);
cmd.Paramaters.AddWithValue("@user_ID",ddlSalesUser.SelectedValue);
...

我将使用where exists (select 1 from xxacl_pN_LEASES_ALL where --your query here)所以它看起来像这样:

Update xxacl_pn_leases_all a set ASSIGNED_TO = '' where exists (select 1 from from xxacl_pN_LEASES_ALL b where a.id=b.id and project_id ....)

查看这个文档。

            string _QueryResult = "your query result";
            if (String.IsNullOrEmpty(_QueryResult))
            {
                //Query has no value
            }
            else
            {
                //Query has some value.
            }

按OP的问题编辑。

方法一:前端 

protected void CmdUpdate_Click(object sender, EventArgs e)
{
    string query = "select mkey from xxacl_pN_LEASES_ALL where project_id = '" + ddlProject.SelectedValue + "' and "+
                "building_id = '" + ddlBuilding.SelectedValue + "' and SALES_USER_ID = '" + ddlSalesUser.SelectedValue + "'";
    SqlConnection con = new SqlConnection(ConStr);
    con.Open();
    SqlCommnad cmd = new SqlCommand(query, con);
    int mkey = Convert.ToInt32(cmd.ExecuteScalar()); //if mkey is numeric
    if (mkey > 0) 
    {
        //Update execution
        query = "Update xxacl_pn_leases_all set ASSIGNED_TO = '' where mkey = mkey";
        SqlCommnad cmd1 = new SqlCommand(query, con);
        cmd1.ExecuteNonQuery();
    }
    cmd.Dispose();
    cmd1.Dispose();
    con.Close();
}

方法二:后台

UPDATE xxacl_pn_leases_all 
   SET ASSIGNED_TO = '' 
   WHERE mkey IN (SELECT mkey FROM xxacl_pN_LEASES_ALL 
                   WHERE project_id = @project_id 
                   AND building_id = @building_id
                   AND SALES_USER_ID = @SALES_USER_ID)

只需通过前端代码执行上述查询。

相关文章：