How to Update to SET Table1.ColumnX = Table2.ColumnX with a where clause

Question

Let's say I have one table called InsuranceMember which contains all members of insurance plans and their spouses. But oh no! for spouses that have a CountryCD of 'Canada' and have a PlanCD of '99999', the MemberNum's (primary key) are all incorrect in this table!

But thankfully, there is a table called InsurancePlan, that contains spouses, and where these Spouses's MemberNum's (also primary key) are all correct!

So in this hypothetical I need a conditional update statement that sets InsuranceMember.MemberNum = InsurancePlan.MemberNum where CountryCD = 'Canada' and PlanCD = '99999'

How can I write a query to make this work and update each row.

When I've tried writing this query I always get back this error: -811 THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE

This is very confusing for me as a beginner in SQL, but I'm sure the situation I'm describing is something many have dealt with.

Edit: Here is an example of code I use that produces that update:

UPDATE PRD.InsuranceMember mem
set mem.MemberNum = ( Select pln.MemberNum
                   from PRD.InsurancePlan pln
                   where mem.MemberNum <> pln.MemberNum
                   and   mem.MoneySource = pln.MoneySource
                     )

WHERE mem.MemberNum = (SELECT pln2.MemberNum FROM PRD.InsurancePlan pln2
                     WHERE mem.ClientCd = pln2.ClientCd
                       AND mem.PLanCd = pln2.PlanCd)
AND Mem.MoneySource='3' 
AND Mem.CountryCd = 'Canada'
AND Mem.PlanCd = '99999'        
;

Answer 1

Try adding a FETCH FIRST ROW ONLY to the end of each of your subqueries, which will inform the database that those expressions will not return multiple values.

UPDATE PRD.InsuranceMember mem
SET mem.MemberNum = ( SELECT pln.MemberNum
               FROM PRD.InsurancePlan pln
               WHERE mem.MemberNum <> pln.MemberNum
               AND   mem.MoneySource = pln.MoneySource
               FETCH FIRST ROW ONLY
                 )
WHERE mem.MemberNum = (SELECT pln2.MemberNum 
               FROM PRD.InsurancePlan pln2
               WHERE mem.ClientCd = pln2.ClientCd
               AND mem.PLanCd = pln2.PlanCd
               FETCH FIRST ROW ONLY
                 )
AND mem.MoneySource='3' 
AND mem.CountryCd = 'Canada'
AND mem.PlanCd = '99999'        
;

Answer 2

Using an UPDATE of a SELECT can help in this situation as you can run just the SELECT part and ensure that the newMemberNum sub-select only returns one row

UPDATE (
    SELECT
        MemberNum
    ,   MoneySource
    ,   CountryCd
    ,   PlanCd
    ,   ( Select pln.MemberNum
           from PRD.InsurancePlan pln
           where mem.MemberNum <> pln.MemberNum
           and   mem.MoneySource = pln.MoneySource
         ) AS NewMemberNum
    FROM
        PRD.InsuranceMember mem
    )
SET MemberNum = NewMemberNum
WHERE
    MoneySource='3' 
AND CountryCd = 'Canada'
AND PlanCd = '99999'        
AND MemberNum IS DISTINCT FROM NewMemberNum

So you might need a MAX(pln.MemberNum) or DISTINCT some such in the sub-select to ensure that at most one row is found for each selected row in PRD.InsuranceMember