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

Extracting data from a table into another table based on a common value – Postgresql

I have a table which somewhat looks like this

Table A:

Voter_id        Id
----------------------
null       |    DEPT 1f7h
null       |    DEPT 3k9n
null       |    DEPT 2lp0
null       |    DEPT 2f6k
(250,000 rows like this)

This table Table A has close to 250,000 rows.

I have another table Table B which looks like this

Name_of_variable |Id      |     value_of_variable
--------------------------------------------------
 Voter_id          |DEPT 1f7h |      12OK9MJL
 First_Name        |DEPT adas |      Umar
 DOB               |DEPT opwe |      20-02-199
 Age               |DEPT jqwq |      24
 Voter_id          |DEPT 90aa |      189H8MLI
 (almost 1 million rows like this)

Table B id column has index

I wanted to fill Voter_id column of Table A using Table B column such that Voter_id column of table A = value_of_variable of Table B where Name_of_variable of Table A is ‘Voter_id’ and TableA.Id=TableB.Id

I have used this query for extracting data and it is working fine on my development database which has 15,000 records in Table A.I want to know if i can further optimize it because it may not work that good on bigger data.

update TableA 
  set Voter_id =(select value_of_variable 
                 from TableB 
                 where Name_of_variable like 'Voter_id' 
                  and  TableA.Id = TableB.id 
                 limit 1);
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have resolved this question by changing my update query like this

    update TableA set Voter_id = TableB.value_of_variable 
from TableB where TableA.id = TableB.id and TableB.Name_of_variable='Voter_id';

  2. 0

    You need to create an index on TableA.Id

    CREATE UNIQUE INDEX Id_idx ON TableA (Id);

    In case your TableA.Id can contain duplicate entries, please remove UNIQUE

    You might also wanna play with

    CREATE UNIQUE INDEX Id_idx ON TableB (Id) INCLUDE (Name_of_variable);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search