>> 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
