SQL Server 2008 update table with values from another table
Solution 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
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
Solution 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
curiosity
I'm a computer scientist and hobby photographer from Görlitz in Saxony, Germany. You can find tips and tutorials about UX, Javascript Canvas, Wordpress and more on my blog: http://digitalewelt.blaustern.bplaced.net/ My photography blog: http://fotos.blaustern.bplaced.net/ Follow me on twitter: @blaustern_foto
Updated on June 05, 2022Comments
-
curiosity almost 2 years
Below you can see my simplified DB model:
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.
I tried this SQL query:
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.
How can I achieve that all Jobs with a certain planid are affected?
-
curiosity over 11 yearsThank you, this works! Just had to add 'where J.planid = 20' in the last row.