CODEWITHSUNDEEP
postgresql
ZedZip
ZedZip

Reputation: 6456

PostgreSql: why this update works incorrectly?

There is table t1 in what I need to replace id with new value.

The 2nd table t_changes contains substitutions old_id->new_id. But when I do UPDATE the t1 contains the same new id value for all records. What is incorrect? The same update works in T-SQL successfully.

drop table t1;
drop table t2;
drop table t_changes;

create table t1
(id INT,name text, new_id INT default(0));

create table t_changes
(old_id INT,new_id int)

insert into t1(id,NAME)
VALUES (1,'n1'),(2,'n2'),(3,'n3');

insert into t_changes(old_id,new_id)
values(1,11),(2,12),(3,13),(4,13)

select * from t1;
select * from t_changes;

-------!!!!
update t1
set new_id = n.new_id
from t1 t 
inner join t_changes n
on n.old_id=t.id;

select * from t1
------------------------------
"id"    "name"  "new_id"
-----------------
"1" "n1"    "11"
"2" "n2"    "11"
"3" "n3"    "11"

Upvotes: 0

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is your Postgres update statement:

update t1
    set new_id = n.new_id
    from t1 t inner join
         t_changes n
         on n.old_id = t.id;

The problem is that the t1 in the update refers to a different t1 in the from. You intend for them to be the same reference. You can do this as:

update t1
    set new_id = n.new_id
    from t_changes n
    where n.old_id = t.id;

Your syntax is fairly close to the syntax supported by some other databases (such as SQL Server). However, for them, you would need to use the table alias in the update:

update t
    set new_id = n.new_id
    from t1 t inner join
         t_changes n
         on n.old_id = t.id;

Upvotes: 1

redneb
redneb

Reputation: 23870

How about doing this instead:

update t1
set new_id = (SELECT new_id FROM t_changes WHERE old_id=id);

Note that if for some row in t1 there is no corresponding row in t_changes, this will change t1.new_id to NULL.

Upvotes: 1

Related Questions