Inserting data from one table based on some Id in postgres

Question

I have two tables with almost 13,000 records and looks something like this

TableA:

ID  Status  Option
----------------------
1 |  Approved  |
2 |  Reject    |
3 |  Approved  |
4
.
.
13,000

TableB

 Name    Option                      Status
-----------------------------------------------
 First  | {'data':'Add into box','ID':'1'}  |  Approved 
 Second | {'data':'Don't Add','ID':'2'}     |  Reject
 Third  | {'data':'Add into box','ID':'3'}  |  Approved 
.
.
.
13,000

I want to fill the Option column (data type varchar)in table A with similar data to that of Table B Option column (data type B) based on same ID which is also in option json object. How do i fill them in one go rather than going one by one.

Your using a RDBMS, so just define the relationship. It TableB add column status_id then update status_id, create a FK to TableA, finally drop status column from TableB. — Belayer
– Belayer, Commented Nov 11, 2022 at 18:23

abhishek kasana · Accepted Answer · 2022-11-11 16:41:57Z

1

An update query where we set the "option" in TableA using a subquery, where we filter the result based on "id" of TableA matching with "id" inside varchar column "option" of TableB.

update tablea 
set option = (select option from tableb 
              where tablea.id::text = tableb.option::json ->> 'id' 
              limit 1);    
-- assuming id has a 1:1 relation in both tables

edited Nov 11, 2022 at 16:41

answered Nov 11, 2022 at 16:14

abhishek kasana

1,53214 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Umar Over a year ago

i think my previous explanation of question was not correct.I have updated the table structure.Can you help in that

abhishek kasana Over a year ago

pls check now. Have updated the access of id from column to varchar json as per your changes.

Umar Over a year ago

yes it has worked,i was trying to do something with joins but your method of adding limit 1 in the end is much easier

Umar Over a year ago

hi is there any way this query can be optimized because it takes lots of time when i have close to 100,000 records

Collectives™ on Stack Overflow

Inserting data from one table based on some Id in postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related