On Thu, Oct 21, 2010 at 5:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tim Uckun <timuckun@gmail.com> writes:
>> I have a very simple update query.
>
>> update cu
>> set screenshot_file_name = tu.screenshot_file_name,
>> screenshot_content_type = tu.screenshot_content_type,
>> screenshot_file_size = tu.screenshot_file_size,
>> screenshot_status = tu.screenshot_status
>> from cu
>> inner join tu on tu.cu_id = cu.id
>
> That isn't actually the query you're issuing, because if it were
> you would get an error "table name "cu" specified more than once".
Sorry I shortened the names of the tables.
> I suspect that the query you're actually issuing involves an
> unconstrained cartesian product self-join between the target table
> and another instance of itself. Postgres doesn't consider that
> the target table should be named again in FROM. But it's hard to
> be sure about that when looking at a redacted query.
I apologize for the redacted query. I was trying to make it easier to follow.
Here is the actual query.
update consolidated_urls
set screenshot_file_name = tu.screenshot_file_name,
screenshot_content_type = tu.screenshot_content_type,
screenshot_file_size = tu.screenshot_file_size,
screenshot_status = tu.screenshot_status
from consolidated_urls cu
inner join trending_urls tu on tu.consolidated_url_id = cu.id
This is a simple inner join.
select count(cu.id)
from consolidated_urls cu
inner join trending_urls tu on tu.consolidated_url_id = cu.id
yields 3657 records.