Thread: UPDATE .. JOIN?

Hi.

I was wondering if I could do something similar to this in Postgres and
if yes how?

UPDATE table1 SET blah = 1 FROM table1
    INNER JOIN table2 ON table1.id = table2.t1id

If not, is there any way to make UPDATE ... WHERE id IN (....) use indexes?

On Jan 12, 2008 5:22 PM, Sergei Shelukhin <realgeek@gmail.com> wrote:
> Hi.
>
> I was wondering if I could do something similar to this in Postgres and
> if yes how?
>
> UPDATE table1 SET blah = 1 FROM table1
>     INNER JOIN table2 ON table1.id = table2.t1id

UPDATE table1 t1
SET blah = 1
FROM table2 t2
WHERE t1.id = t2.t1id

> If not, is there any way to make UPDATE ... WHERE id IN (....) use indexes?

It depends. Read the docs:

http://www.postgresql.org/docs/8.2/static/using-explain.html
http://www.postgresql.org/docs/8.2/static/planner-stats.html
http://www.postgresql.org/docs/8.2/static/planner-stats-details.html

Rodrigo E. De León Plicet wrote:
> On Jan 12, 2008 5:22 PM, Sergei Shelukhin <realgeek@gmail.com> wrote:
>
>> Hi.
>>
>> I was wondering if I could do something similar to this in Postgres and
>> if yes how?
>>
>> UPDATE table1 SET blah = 1 FROM table1
>>     INNER JOIN table2 ON table1.id = table2.t1id
>>
>
> UPDATE table1 t1
> SET blah = 1
> FROM table2 t2
> WHERE t1.id = t2.t1id
>
>
Hmmm. What if there's more than one table? Is "from x,y" a viable option?

On Jan 12, 2008 11:26 PM, Sergei Shelukhin <realgeek@gmail.com> wrote:
> Hmmm. What if there's more than one table? Is "from x,y" a viable option?

UPDATE table1 t1
SET blah = 1
FROM (
  SELECT t2.t1id
  FROM table2 t2
  JOIN table3 t3
  ON t2.id = t3.t2id
) foobar
WHERE t1.id = foobar.t1id

It's all in the docs:
http://www.postgresql.org/docs/8.2/static/sql-update.html

Good luck.

On Jan 13, 2008 12:05 AM, I said
> It's all in the docs:
> http://www.postgresql.org/docs/8.2/static/sql-update.html

To clarify, you can use the direct form, without using a subselect:

UPDATE table1 t1
SET blah = 1
FROM table2 t2
  JOIN table3 t3
  ON t2.id = t3.t2id
WHERE t1.id = t2.t1id

Lookup "fromlist" (without the quotes) on the quoted link.

Regards.

Re: Updating a table with joins
I have been trying to achieve the same thing for quite a period of months
but
could not get the right query, finally now i am sorted, this one works like
a charm for me.
========================================
Replace the tables on this query with joined tables

Update service_reminderschedule srs
set reminder_sent = false
from (
    select ss.fk_man_service_id, ss.serviceschedule_id, v.vehicle_id
        from serviceschedule ss
        inner join manufactureservices ms
        on ms.man_service_id = ss.fk_man_service_id
        inner join vehicle_model vm
        on vm.model_id = ms.fk_vehicle_model_id
        inner join vehicle_info v
        on v.fk_vehicle_model_id = vm.model_id
        where ms.fk_vehicle_model_id = 1) ss
where ss.vehicle_id = srs.fk_vehicle_id
and srs.fk_serviceschedule_id = ss.serviceschedule_id and srs.date_sent is
null;



--
View this message in context: http://postgresql.1045698.n5.nabble.com/UPDATE-JOIN-tp1895125p5751783.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Replace the tables on this query with your joined tables

Update service_reminderschedule srs
set reminder_sent = false
from (
        select ss.fk_man_service_id, ss.serviceschedule_id, v.vehicle_id
                from serviceschedule ss
                inner join manufactureservices ms
                on ms.man_service_id = ss.fk_man_service_id
                inner join vehicle_model vm
                on vm.model_id = ms.fk_vehicle_model_id
                inner join vehicle_info v
                on v.fk_vehicle_model_id = vm.model_id
                where ms.fk_vehicle_model_id = 1) ss
where ss.vehicle_id = srs.fk_vehicle_id
and srs.fk_serviceschedule_id = ss.serviceschedule_id and srs.date_sent is
null;



--
View this message in context: http://postgresql.1045698.n5.nabble.com/UPDATE-JOIN-tp1895125p5751790.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.