使用condition更新表上的行

本文关键字:condition 更新 使用 | 更新日期: 2023-09-27 18:03:47

我试图删除所有行,从使用条件的表底部开始,但是当满足这些条件时,我希望它停止更新表并保留其余部分。例如,如果表上的最后一个条目满足条件,则删除它,如果它后面的条目不满足条件,则停止并退出循环。这是我得到的代码,但它删除了所有的行:

 private void button1_Click(object sender, EventArgs e)
    {
        MessageBox.Show("A atualizar dados");
        bool check = true;
        do
        {
            string connectionString = @"Data Source=.'wintouch;Initial Catalog=bbl;User ID=sa;Password=Pa$$w0rd";
            string queryString = string.Empty;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                queryString = "DELETE FROM wgcdoccab 
                WHERE serie ='1' AND tipodoc ='FSS' 
                AND contribuinte ='999999990' 
                and datadoc = CONVERT(varchar(10),(dateadd(dd, -2, getdate())),120)"
                SqlCommand command = new SqlCommand(queryString, connection);
                //command.Connection.Open();
                command.ExecuteNonQuery();
            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                queryString = "SELECT * FROM wgcdoccab 
                WHERE serie !='1' and tipodoc !='FSS'
                and contribuinte !='999999990' 
                and  datadoc != CONVERT(varchar(10),(dateadd(dd, -1, getdate())),120) ";
                using (SqlCommand command = new SqlCommand(queryString, connection))
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        check = true;
                    }
                    else
                    {
                        check = false;
                        MessageBox.Show("Dados Apagados com sucesso");
                    }
                    command.Connection.Close();
                }
            }
        }
        while (check);

使用condition更新表上的行

试试下面的例子:

DELETE
FROM tableName
WHERE ID > 
(
    SELECT MAX(ID)
    FROM tableName
    WHERE condition = false
)

例如,如果要删除直到值为4:

DELETE
FROM tableName
WHERE ID > 
(
    SELECT MAX(ID)
    FROM tableName
    WHERE tableName.Value = 4
)

如果表行为:

|ID|Value|
| 1|    7|
| 2|    4|

则子选择将为2,并且不删除任何行。但是,如果行是:

|ID|Value|
| 1|    7|
| 2|    4|
| 3|    9|
| 4|    1|

则子选择仍将返回ID 2,最后2行将被删除。