将旧数据存储在同一表的一列中并更新另一列中的新数据

发布于 2024-12-03 12:59:39 字数 635 浏览 1 评论 0原文

我不是专业程序员，而是需要帮助的爱好者。这是我的挑战：

我有一个充满员工数据的表。

目前，我们的任何员工都可以转到我们网站上的“更新”网页，然后在文本框中显示的旧姓氏上输入他的新姓氏然后单击“提交”，他的姓氏将在数据库中更新。但是，我的主管希望将员工的旧姓氏与新姓氏保留在同一个表中。她的想法是，当员工在文本框中输入他的新姓氏时，它将触发数据库将他的以前姓氏存储在名为“别名”的列中，然后在“姓氏”列中更新他的新姓氏。我该如何继续呢？

以下是表格的结构：

PeopleID (int)  
JobIDNum (int)  
EmployeeIDNum (varchar(25))  
Email (varchar(100))  
Password (varchar(50))  
LastName (varchar(50))  
FirstName (varchar(25))  
Deleted (char(1))  
Alias (varchar(50))

我感谢我能得到的任何帮助和/或建议。

原文

I'm not a professional programmer but an enthusiast who needs help. Here is my challenge:

I have a table full of data for our employees.

Currently, any of our employees can go to the "Update" web page on our web site and type in his new last name over the previous one that is displayed in a textbox and click submit, his last name will be updated in the database. However, my supervisor wants to keep the employees' previous last name in the same table as the new last name. Her idea is when an employee types in his new last name in the textbox, it will trigger the database to store his previous last name in a column called "Alias" and then update his new last name in the "LastName" column. How do I proceed with that?

Here is the structure of the table:

PeopleID (int)  
JobIDNum (int)  
EmployeeIDNum (varchar(25))  
Email (varchar(100))  
Password (varchar(50))  
LastName (varchar(50))  
FirstName (varchar(25))  
Deleted (char(1))  
Alias (varchar(50))

I appreciate any assistance and/or advice I can get.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故事与诗 2024-12-10 12:59:39

UPDATE TABLENAME
  SET Alias = LastName, LastName = @LastName 
WHERE PeopleID = @PeopleID

其中@LastName是具有new值的参数，@PeopleID是相应人员的ID。

UPDATE TABLENAME
  SET Alias = LastName, LastName = @LastName 
WHERE PeopleID = @PeopleID

Where the @LastName is a parameter with new value, @PeopleID is a id of corresponding people.

回复收藏 0 原文

所有深爱都是秘密 2024-12-10 12:59:39

尝试这个更新语句：

UPDATE Employee
Set LastName = @NewLastName,
    Alias = LastName
WHERE PeopleId = @Whatever

Try this update statement:

UPDATE Employee
Set LastName = @NewLastName,
    Alias = LastName
WHERE PeopleId = @Whatever

回复收藏 0 原文

苦笑流年记忆 2024-12-10 12:59:39

建议您可以从 ASP 调用一个存储过程。

 CREATE PROC UpdateEmp
    @EmpID  int,
    @LastName varchar(50),
    --all the others properties you need to update this person
 AS
 BEGIN

     UPDATE Employee
     SET Alias = LastName,
         LastName = @LastName,
         --all the other properties you want updated.
     WHERE PeopleID = @EmpID;

 END

在 ASP 代码中调用此存储过程，如下所示：

Dim cmd
Dim ln
Dim retCount    
Dim conn
Set conn= Server.CreateObject("ADODB.Connection")       
Set cmd = Server.CreateObject("ADODB.Command")      
conn.Open "some connection string"
With cmd
    .ActiveConnection = conn
    .Commandtext = "UpdateEmp"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@EmpID", adInteger, adParamInput, 10)
    .Parameters("@EmpID") = 22 'some Employee you get from your code
    .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 50)
    .Parameters("@LastName") = "MyLastName" 'some Employee you get from your code
    .Execute ln, , adExecuteNoRecords
End With        
Set cmd = Nothing

Suggest a stored procedure that you can call from ASP.

 CREATE PROC UpdateEmp
    @EmpID  int,
    @LastName varchar(50),
    --all the others properties you need to update this person
 AS
 BEGIN

     UPDATE Employee
     SET Alias = LastName,
         LastName = @LastName,
         --all the other properties you want updated.
     WHERE PeopleID = @EmpID;

 END

Call this stored proc in your ASP code like this:

Dim cmd
Dim ln
Dim retCount    
Dim conn
Set conn= Server.CreateObject("ADODB.Connection")       
Set cmd = Server.CreateObject("ADODB.Command")      
conn.Open "some connection string"
With cmd
    .ActiveConnection = conn
    .Commandtext = "UpdateEmp"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@EmpID", adInteger, adParamInput, 10)
    .Parameters("@EmpID") = 22 'some Employee you get from your code
    .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 50)
    .Parameters("@LastName") = "MyLastName" 'some Employee you get from your code
    .Execute ln, , adExecuteNoRecords
End With        
Set cmd = Nothing

回复收藏 0 原文

~没有更多了~