CODEWITHSUNDEEP
postgresqljoinsql-update
Olivier Pons
Olivier Pons

Reputation: 15778

How can I update a column based on a value of another table in one query?

I have:

I want to remove personshallow and have entity_ptr_id in person

So I've made a column entity_ptr_id in person, and I would like to do this query:

"for each personshallow_ptr_id of person, take its corresponding entity_ptr_id in personshallow and assign it to entity_ptr_id of person

Here's the create table if you have not enough information:

create table person
(
    personshallow_ptr_id integer not null
        constraint person_personshallow_ptr_id_d7009fc0_pk
            primary key
        constraint person_personshallow_ptr_id_d7009fc0_uniq
            unique
        constraint person_personshallow_ptr_id_d7009fc0_fk_perso
            references personshallow
            deferrable initially deferred
)

create table personshallow
(
    entity_ptr_id integer not null
        constraint personshallow_pkey
            primary key
        constraint personshallow_entity_ptr_id_b43d70c2_fk_entity_id
            references entity
            deferrable initially deferred
);

The main objective is to remove the table personshallow

Upvotes: 0

Views: 52

Answers (1)

Jaisus
Jaisus

Reputation: 1109

Maybe somthing like this ? (your code is not working for table creation so I assume there is something to identify which person is in personshallow)

UPDATE person c
SET entity_ptr_id = c2.entity_ptr_id 
FROM personshallow c2
WHERE c.personshallow_ptr_id  = c2.personshallow_ptr_id

EDIT So, after discussion : 

ALTER TABLE public.app_person
DROP CONSTRAINT app_person_personshallow_ptr_id_d7009fc0_fk_app_perso;

After that drop your person_shallow table, rename your column if needed, and create a new foreign key on your person table ;)

Upvotes: 1

Related Questions