在SQL Server中更新相同的表

I was trying to update the same table data from same table data.

我试图从相同的表数据更新相同的表数据。

My SP is as shown below :

我的SP如下所示：

UPDATE T1
SET T1.Name = T2.Name
   , T1.Age = T2.Age
   , T1.Subject = T2.Subject
FROM Student T1
   , Student T2
WHERE T1.StudentID = @OldID
   AND T2.StudentID = @NewID

When I am executing this query, there is no error. But the update is not working.

当我执行此查询时，没有错误。但更新无效。

[here NO Common column values to compare like T1.StudentID = T2.StudentID]

[这里没有要比较的公共列值，如T1.StudentID = T2.StudentID]

3 个解决方案

#1

Try this:

尝试这个：

UPDATE t1
SET t1.name = t2.name, t1.age = t2.age, t1.subject = t2.subject
FROM student t1
INNER JOIN
student t2
ON t1.StudentID = @oldID
AND t2.StudentID = @NewID

The full example is here

完整的例子就在这里

#2

Try this one , you are using wrong alias .

试试这个，你使用了错误的别名。

UPDATE T1 SET Name = T2.Name
, Age = T2.Age
, Subject = T2.Subject
 FROM Student T1
, Student T2
WHERE T1.StudentID = @OldID
AND T2.StudentID = @NewID

#3

try to do it through a stored procedure, declare some variables, load the new values to these variables, then update your table.

尝试通过存储过程来执行它，声明一些变量，将新值加载到这些变量，然后更新表。

Should look something like that:

看起来应该是这样的：

CREATE PROCEDURE dbo.StoredProcedure2

    @OldID int,
    @NewID int

AS

declare @Name text, @Age int, @Subject text

begin

select @Name = T1.Name, @Age = T1.Age, @Subject= T1.Subject
from Student T1
Where T1.StudentID = @OldID

end

begin

UPDATE T1 SET T1.Name = @Name, T1.Age = @Age, T1.Subject = @Subject
FROM Student T1
WHERE T1.StudentID = @OldID

end
    RETURN

#1