更新2表的查询 [英] Update query for 2 table

问题描述

大家好吧

i下面有这个查询

Hi All
i have this Query Below

string updateReq = "UPDATE Student SET Student.ID_Request = Request.ID_Request FROM Request where Student.St_Code=Request.St_Code   INNER JOIN  Request ON Student.ID_Request = Request.ID_Request";

但是当我执行此操作时出现此错误：

but when i excute this i get this error :

The objects "Request" and "Request" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

我该如何解决这个问题？



我尝试了什么：



i尝试选择临时表中的记录但它不起作用或者我可以这样做错误

how can i solve this ?

What I have tried:

i tried to select the records in the temp table but it doesn't work or maybe i do it wrong

string select = "SELECT * INTO #TempTable FROM Student s where ID_Request IS NULL";

string updateReq = "UPDATE TempTable SET s.ID_Request = Request.ID_Request FROM Request where Student.St_Code=Request.St_Code   INNER JOIN  Request ON Student.ID_Request = Request.ID_Request";

当我执行这些字符串时我得到同样的错误

when i excute these string i get the same error

推荐答案

UPDATE Student SET Student.ID_Request = R1.ID_Request
     FROM Request R1  
          INNER JOIN Request R2 
             ON (Student.ID_Request = R2.ID_Request) 
          INNER JOIN  Student 
             ON (Student.St_Code=R2.St_Code) 

你必须区分同一个表的两个实例。我会尝试帮助，但我在手机上这样做...

You have to distinguish between the two instances of the same table. I will try to help but I'm doing this on a phone...

string updateReq = "UPDATE Student SET Student.ID_Request = R1.ID_Request FROM Request R1 where Student.St_Code=Request.St_Code INNER JOIN Request R2 ON Student.ID_Request = R2.ID_Request";

我无法对此进行测试，但请注意，我已经为每个表提供了别名。

I can't test this but note that I've given the tables an alias each.

这篇关于更新2表的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！