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

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

Carlo Stonebanks wrote:
> 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?

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

Yours,
Laurenz Albe

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

On Mon, Oct 15, 2007 at 03:23:59PM -0400, Carlo Stonebanks wrote:
> Would someone be kind enough to tell me if there is somethign wrong with
> this apporach:

Can't see anything obviously wrong, but if you want more help you'll
need to provide the EXPLAIN output.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.