Question 89443 in PostgreSQL Question posted in
The official documentation can be found here.

Using ctid to update table – Postgresql

I translate t-sql code to PostgreSQL. I have temp table, which structure i can’t change. This table have only index without PK. In t-sql to update columns in this table i using this:

update t
set col_1 = tt.val
from table_1 t 
inner join table_2 tt on t.col_2 = tt.col_2

But in Postgres i need join my temp table with themself for update, so can I use ctid column for it (instead of PK), or this method have problems? I read that vacuum can change ctid.

update table_1
set col_1 = tt.val
from table_1 t 
inner join table_2 tt on t.col_2 = tt.col_2
where table_1.ctid = t.ctid

upd: update question

Tags:

2

Answers


  1. 0

    The WITH cte used to get the id and val from table_2, then we can update col1 using update join

    Your First Query can be translated to something like this :

    with cte as (
  select t.id, tt.val
  from table_1 t 
  inner join table_2 tt on t.id = tt.t_id
)
update table_1 t
set col_1 = cte.val
from cte
where t.id = cte.id
    Login or Signup to reply.
  2. 0

    You want to update values in table_1 based on data in table_2. In Postgres you should not repeat the target table in the FROM clause. So the original T-SQL UPDATE statement translates to the following in Postgres:

    update table_1
  set col_1 = tt.val
from table_2 tt 
where table_1.col_2 = tt.col_2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search