SQL Server 2008 update table使用来自另一个表的值

Below you can see my simplified DB model:

下面是我简化的DB模型:

Due to an error there are only null-values in the column Job.location where the entries belong to a certain Plan. Therefore I want to update all Jobs associated with this Plan, setting Job.location to Location.name of the User, who owns this Plan.

由于错误，列作业中只有空值。条目属于某个计划的位置。因此，我想更新与此计划相关的所有作业，设置作业。位置到location。name用户名，拥有此计划的用户名。

I tried this SQL query:

我尝试了这个SQL查询:

    update dbo.Job set location =

        (select name from dbo.Location as loc where

           loc.objectid = objectid  and loc.user_id in 

           (select userid from dbo.[Plan] as p where p.planid = 20))

        where planid = 20

However, the result is always: 0 rows affected. The subqueries itself work correctly.

但是，结果总是:受影响的行为0。子查询本身工作正常。

How can I achieve that all Jobs with a certain planid are affected?

我怎样才能做到所有的工作都有一个planid的影响?

2 个解决方案

#1

I think you mistake may be that you have no alias for objectid column in subquery loc.objectid = objectid, so when you running subquery by itself, it just works like loc.objectid = loc.objectid and when you running it in the update, it works like loc.objectid = dbo.Job.objectid

我想你可能搞错了，在子查询loc中没有objective列的别名。objectid = objectid，所以当你自己运行子查询时，它就像loc一样工作。objectid = loc。当你在更新中运行它时，它就像loc一样运行。objectid = dbo.Job.objectid

In your schema it's possible to have multiple locations for users, but supposing you have only one location per user and object, you can try this query:

在您的模式中，为用户提供多个位置是可能的，但是假设每个用户和对象只有一个位置，您可以尝试这个查询:

update dbo.Job set
    location = L.Name
from dbo.Job as J
    inner join dbo.[Plan] as P on P.planid = J.planid
    inner join dbo.Location as L on L.user_id = P.userid and L.objectid = J.objectid

#2

UPDATE
j
SET Job.location = loc.name
FROM
Job j
INNER JOIN Plan p ON j.planid = p.planid
INNER JOIN aspnet_Users u ON p.userid = u.UserId
INNER JOIN Location loc ON u.UserId = loc.user_id
WHERE j.planid = 20 
AND p.planid = 20