title: Update/Merge multiple rows with select statement data
body: <p>Im trying to update a single table with output of a select statement which returns two values. One is the accountid that I need to update in the table, and the other is the information I need to update.</p>
  
  <pre><code>Here is my select statement
  select CTP.CARETEAMPATIENTID, O.ORGANIZATIONID
    from acts.careteampatient ctp,  
         ods.member m,
         ods.supplierorganization so,
         ods.MASTERSUPPLIERSUPPLIERRELATION mssr,
         ods.INSURANCEORGSUPPLIERRELATION   IOSR, 
         ods.INSURANCEORGANIZATION          IO,
         acts.organization o
  where ctp.accountorgid is null
      and m.primarymemberplanid = ctp.primarymemberplanid
      and SO.AHMSUPPLIERID = M.AHMSUPPLIERID
      and mssr.SUPPLIERID       = so.SUPPLIERORGID
      AND iosr.SUPPLIERID       = so.SUPPLIERORGID
      AND io.INSURANCEORGID     = iosr.INSURANCEORGID
      and io.processingmodecd  = 'P'
      and so.usagemnemonic     = 'P'
      and O.ODSACCOUNTID = IO.INSURANCEORGID
      and O.ACCOUNTFLG = 'Y'
  </code></pre>
  
  <p>I want to do something along the lines of </p>
  
  <pre><code>update careteampatient
  from (select CTP.CARETEAMPATIENTID patientid, O.ORGANIZATIONID orgid
    from acts.careteampatient ctp, 
         ods.member m,
         ods.supplierorganization so,
         ods.MASTERSUPPLIERSUPPLIERRELATION mssr,
         ods.INSURANCEORGSUPPLIERRELATION   IOSR,
         ods.INSURANCEORGANIZATION          IO,
         acts.organization o
  where ctp.accountorgid is null
      and m.primarymemberplanid = ctp.primarymemberplanid
      and SO.AHMSUPPLIERID = M.AHMSUPPLIERID
      and mssr.SUPPLIERID       = so.SUPPLIERORGID
      AND iosr.SUPPLIERID       = so.SUPPLIERORGID
      AND io.INSURANCEORGID     = iosr.INSURANCEORGID
      and io.processingmodecd  = 'P'
      and so.usagemnemonic     = 'P'
      and o.odsaccountid = io.insuranceorgid
      and o.accountflg = 'Y') b  
  set a.accountorgid = b.orgid
  where a.careteampatientid = b.patientid
  </code></pre>
  
  <p>Here is the Merge I also tried</p>
  
  <pre><code>merge into careteampatient a
  using (select CTP.CARETEAMPATIENTID patientid, O.ORGANIZATIONID orgid
          from acts.careteampatient ctp, 
               ods.member m,
               ods.supplierorganization so,
               ods.MASTERSUPPLIERSUPPLIERRELATION mssr,
               ods.INSURANCEORGSUPPLIERRELATION   IOSR,
               ods.INSURANCEORGANIZATION          IO,
               acts.organization o
        where ctp.accountorgid is null
            and m.primarymemberplanid = ctp.primarymemberplanid
            and SO.AHMSUPPLIERID = M.AHMSUPPLIERID
            and mssr.SUPPLIERID       = so.SUPPLIERORGID
            AND iosr.SUPPLIERID       = so.SUPPLIERORGID
            AND io.INSURANCEORGID     = iosr.INSURANCEORGID
            and io.processingmodecd  = 'P'
            and so.usagemnemonic     = 'P'
            and o.odsaccountid = io.insuranceorgid
            and o.accountflg = 'Y') b
  on a.careteampatientid = b.patientid
  when matched then
    update 
      set a.accountorgid = b.orgid
  </code></pre>
  
  <p>This isnt working and Im fresh out of ideas, any help would be awesome. Thanks</p>