I am attempting to overwrite a field value with the value from another table where a certain condition is true. I have mocked up my code below
Pseudocode:
x
Where an employee has the team Ops in old_data, get their new team from new_data and overwrite the team in old_data
My Code:
UPDATE old_data -- This has columns Employee, Latest_Team
SET
Latest_Team =
(select new_data.team
from new_data
left join old data
ON old_data.employee = new_data.employee
)
WHERE old_data.employee = 'Ops'
But this is returning the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I’m not sure where i am going wrong exactly
Advertisement
Answer
If you are looking for the team “ops”, then your query has several issues:
- You are filtering on the employee, not the team.
- You have a typo in the inner
from
clause. - You want a correlated subquery.
So, I think you want:
UPDATE old_data -- This has columns Employee, Latest_Team
SET Latest_Team = (select nd.team
from new_data nd
where old_data.employee = nd.employee
)
WHERE od.latest_team = 'Ops';
------^ I think this is the filter you describe