MySQL-UPDATE查询不影响行
本文关键字:影响 查询 MySQL-UPDATE | 更新日期: 2024-10-18 16:43:32
我的更新查询不起作用,即使它没有显示任何错误,只有没有行受到影响/匹配:
UPDATE `Budget` SET `amount` = 500, `rest` = 500 WHERE `company_number` = 1 AND `section_number` = 1 AND `chapter_number` = 1 AND `article_number` = 3 AND `subarticle_number` = 0 AND `ssubarticle_number` = 0
但当我从数据库中进行"SELECT"时,我得到了一行返回:
SELECT * FROM Budget WHERE `company_number` = 1 AND `section_number` = 1 AND `chapter_number` = 1 AND `article_number` = 3 AND `subarticle_number` = 0 AND `ssubarticle_number` = 0
这是"预算"表:
CREATE TABLE `budget` (
`budget_id` int(11) NOT NULL AUTO_INCREMENT,
`company_number` int(11) NOT NULL DEFAULT '0',
`section_number` int(11) NOT NULL DEFAULT '0',
`chapter_number` int(11) NOT NULL DEFAULT '0',
`article_number` int(11) NOT NULL DEFAULT '0',
`subarticle_number` int(11) NOT NULL DEFAULT '0',
`ssubarticle_number` int(11) NOT NULL DEFAULT '0',
`name` varchar(90) DEFAULT NULL,
`amount` double DEFAULT NULL,
`rest` double DEFAULT NULL,
`insert_date` date DEFAULT NULL,
`transfer_date` date DEFAULT NULL,
`has_children` bit(1) DEFAULT NULL,
PRIMARY KEY (`budget_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
我用来运行查询的方法。
public bool UpdateTransfer(string companyNumber, string sectionNumber, string transferNumber, string TransferDate,
string[] budgetRow, string oldAmount, string newAmount)
{
MySqlTransaction tr = connection.BeginTransaction();
cmdQuery = connection.CreateCommand();
cmdQuery.Connection = connection;
cmdQuery.Transaction = tr;
try
{
cmdQuery.CommandText = @"SET SQL_SAFE_UPDATES = 0;";
cmdQuery.ExecuteNonQuery();
// First, we return the old amount to the source row budget
cmdQuery.CommandText = @"UPDATE Budget SET `rest` = `rest` + " + oldAmount +
" WHERE `company_number` = " + companyNumber + " AND `section_number` = " + sectionNumber +
" AND `chapter_number` = " + budgetRow[0] + " AND `article_number` = " + budgetRow[1] +
" AND `subarticle_number` = " + budgetRow[2] + " AND `ssubarticle_number` = " + budgetRow[3];
cmdQuery.ExecuteNonQuery();
MessageBox.Show(cmdQuery.CommandText);
// Second, we make the transfer and substract the new amount from the source row budget
cmdQuery.CommandText = @"UPDATE `Budget` SET `rest` = `rest` - " + newAmount +
" WHERE `company_number` = " + companyNumber + " AND `section_number` = " + sectionNumber +
" AND `chapter_number` = " + budgetRow[0] + " AND `article_number` = " + budgetRow[1] +
" AND `subarticle_number` = " + budgetRow[2] + " AND `ssubarticle_number` = " + budgetRow[3];
cmdQuery.ExecuteNonQuery();
MessageBox.Show(cmdQuery.CommandText);
// Third, update the rest and new amount of destination row budget
cmdQuery.CommandText = @"UPDATE `Budget` SET `amount` = " + newAmount + ", `rest` = " + newAmount +
" WHERE `company_number` = " + companyNumber + " AND `section_number` = " + sectionNumber +
" AND `chapter_number` = " + budgetRow[4] + " AND `article_number` = " + budgetRow[5] +
" AND `subarticle_number` = " + budgetRow[6] + " AND `ssubarticle_number` = " + budgetRow[7];
cmdQuery.ExecuteNonQuery();
MessageBox.Show(cmdQuery.CommandText);
// Last step, update the transfer table.
cmdQuery.CommandText = @"UPDATE `Transfer` SET `amount` = " + newAmount + ", `transfer_date` ='" + TransferDate + "'" +
" WHERE `transfer_number` = " + transferNumber +
" AND `company_number` = " + companyNumber + " AND `section_number` = " + sectionNumber +
cmdQuery.ExecuteNonQuery();
MessageBox.Show(cmdQuery.CommandText);
cmdQuery.CommandText = @"SET SQL_SAFE_UPDATES = 1;";
cmdQuery.ExecuteNonQuery();
tr.Commit();
return true;
}
catch (MySqlException ex)
{
MessageBox.Show("error " + ex.ErrorCode.ToString());
tr.Rollback();
return false;
}
}
}
下面是一个如何在C#上执行sql后验证是否有行更改的示例
public static Boolean PerformDatabaseAction(string sqlQuery)
{
using (SqlConnection con = DbConnect()) // Get SqlConnection
{
SqlCommand cmd = new SqlCommand(sqlQuery);
cmd = new SqlCommand(sqlQuery);
CommandType = System.Data.CommandType.Text;
Connection = con;
int rows = cmd.ExecuteNonQuery(); // Gets the count of affected rows
if(rows > 0)
return true;
else
return false;
}
}
您的查询有效,请在这个查询上尝试。