Update Rows Where the following conditions are true

Question

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

(1) Please tag your question with the database that you are using (2) Sample data and expected results would sure help others understand what you exactly are looking for. — GMB, Jan 27 '20 at 14:27
Your select statement returns more than 1 row. So latest Team can not be matched with 1 and only 1 row — apomene, Jan 27 '20 at 14:29

score 0 · Answer 1 · answered Jan 27 '20 at 14:29

Make sure that this query doesnt return anyting and your code will work

select new_data.team, count(*)
            from new_data
            left join old data WHERE old_data.employee = 'Ops'
group by new_data.team
having count(*) > 1

If you have more than one team per employee with Old_data.employee = 'Ops', sql wont know which to choose for that employee for the update

score 0 · Answer 2 · answered Jan 27 '20 at 14:38

You got a typo in your join - "old data" should be "old_data".

Maybe this can help you: update columns values with column of another table based on condition

In your example it would be:

UPDATE old_data SET
    Latest_Team = (SELECT new_data.team FROM new_data WHERE old_data.employee = new_data.employee LIMIT 1)
    WHERE old_data.employee = 'Ops';

score 0 · Accepted Answer · answered Jan 27 '20 at 15:02

0

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

answered Jan 27 '20 at 15:02

Gordon Linoff

1,242,037
58
646
786

Thank you, this works. How would you have done this with a join? – andre1990 Jan 27 '20 at 17:00
@andre1990 . . . Many databases don't support `join` syntax in `update`. And those that do have different syntaxes. There is no way to answer the question in your comment generically. – Gordon Linoff Jan 27 '20 at 17:47

Update Rows Where the following conditions are true

3 Answers3