If I do an update using the FROM clause, and that clause has a sub-query
that refers to the table I am updating, will I be waiting for ever for a
table to lock to release?
The update before seems to stall, and it occurs to me that perhaps this is
the problem.
BTW - I rarely do anything but the most straightfoward updates - am I
mistaken as to the logic of how this will work?
Carlo
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
