Update Rows Where the following conditions are true

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:

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

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