UPDATE .. JOIN?

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general