如何在SQL Server中使用UPDATE
本文关键字:UPDATE Server SQL | 更新日期: 2023-09-27 17:59:24
我正在使用ASP进行一个项目。NET Web窗体。我目前正在项目的更新部分。我想要这样的数据:
时钟
ID | EmpID | Date | Clock IN | Morning IN | Morning OUT | Clock OUT
1 | 1 | 07/20/16 | 07:00 am | 09:00 am | 09:15 am | 11:00 am
2 | 1 | 07/21/16 | 08:00 am | 10:00 am | 10:15 am | 12:00 pm
但我得到的是
时钟
ID | EmpID | Date | Clock IN | Morning IN | Morning OUT | Clock OUT
1 | 1 | 07/20/16 | 07:00 am | 10:00 am | 10:15 am | 12:00 pm
2 | 1 | 07/21/16 | 08:00 am | 10:00 am | 10:15 am | 12:00 pm
这是代码:
插入
protected void cmdCIN_Click(object sender, EventArgs e)
{
myConnection.Open();
Response.Write("Welcome!");
string query = "Insert into [dbo].[Clock] (EmpID,Date,ClockIN) Values ((select EmpID from Emp_Record where ID = @user), @date, @cin)";
SqlCommand insertCommand = new SqlCommand(query, myConnection);
insertCommand.Parameters.AddWithValue("@user", txtID.Text);
insertCommand.Parameters.AddWithValue("@date", DateTime.Now.ToShortDateString());
insertCommand.Parameters.AddWithValue("@cin", DateTime.Now.ToLongTimeString());
insertCommand.ExecuteNonQuery();
myConnection.Close();
}
更新
protected void cmdAMIN_Click(object sender, EventArgs e)
{
Response.Write("You have started your first break");
myConnection.Open();
string query = "UPDATE Clock SET MorningIN = @AMIN FROM Emp_Record INNER JOIN Clock ON Em_Record.EmpID = Clock.LeadID;";
SqlCommand insertCommand = new SqlCommand(query, myConnection);
insertCommand.Parameters.AddWithValue("@AMIN", DateTime.Now.ToLongTimeString());
myConnection.Close();
}
我将如何使用update命令,以便其他数据不会影响第一个数据?
您需要使用Where语句来限定更新代码,该语句告诉数据库要更新哪一行。否则,将更新所有行。
EX:"UPDATE Clock SET MorningIN = @AMIN FROM Emp_Record INNER JOIN Clock ON Em_Record.EmpID = Clock.LeadID WHERE ID = @idtoupdate;";
您的问题与ASP无关。NET,但是SQL。
要进行故障排除,请使用SQLServerManagementStudio(或您最喜欢的SQL工具)并执行
declare @user int
set @user=1 -- value you enter in txtID.Text
declare @date date
set @date=GETDATE()
declare @cin time
set @cin=GETDATE()
Insert into [dbo].[Clock] (EmpID,Date,ClockIN)
Values ((select EmpID from Emp_Record where ID = @user), @date, @cin)
然后
declare @AMIN time
set @AMIN=GETDATE()
UPDATE Clock SET MorningIN = @AMIN FROM Emp_Record
INNER JOIN Clock ON Em_Record.EmpID = Clock.LeadID;
最后一个查询看起来是错误的,很可能会失败。尝试将其修改为
UPDATE Clock SET MorningIN = @AMIN FROM Emp_Record t1
INNER JOIN Clock t2 ON t1.EmpID = t2.ID;
然后再试一次。如果它能工作,它将为所有行更新具有相同值的MorningIN。如果只需要更新特定的用户,那么应该在WHERE子句中提供他的ID,类似于第一个查询。不确定逻辑是什么,但可能是例如
UPDATE Clock SET MorningIN = @AMIN FROM Emp_Record t1
INNER JOIN Clock t2 ON t1.EmpID = t2.ID AND t2.ID = @XXX