Re: Will UPDATE lock if FROM refers to target table?

>> You cannot block yourself with a lock, if that's what you mean.
>> The locks your session takes out will only block other sessions.

Well, that's the GOOD news! The bad news is - I'm not sure whether there's
somethign wrong with the logic of this query. The sub-query inside the FROM
is correct, is this logic consistent with how UPDAT.. FROM should work (all
of the samples I see are incredibly simplistic and show assignment and
reference to literals, not SET or WHERE clauses that are dependent on
anything in the FROM clause).

Would someone be kind enough to tell me if there is somethign wrong with
this apporach:

UPDATE mdx_core.provider_practice
   SET default_postal_code = def.postal_code,
   default_state_code = def.state_code,
   default_country_code = 'US'
FROM
   (SELECT provider_id,
      provider_practice_id,
      substr(coalesce(a.postal_code, f.default_postal_code), 1, 5) as
postal_code,
      coalesce(a.state_code, f.default_state_code) as state_code
   FROM mdx_core.provider_practice as pp
   JOIN mdx_core.facility as f
   ON f.facility_id = pp.facility_id
   LEFT JOIN mdx_core.facility_address as fa
   ON fa.facility_address_id = pp.facility_address_id
   LEFT JOIN mdx_core.address as a
   ON a.address_id = fa.address_id
   WHERE coalesce(a.country_code, f.default_country_code) = 'US'
   ) as def
WHERE provider_practice.provider_practice_id = def.provider_practice_id

Thanks,

Carlo