CODEWITHSUNDEEP
postgresql
Umar
Umar

Reputation: 59

Inserting data from one table based on some Id in postgres

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.

Upvotes: 0

Views: 115

Answers (1)

abhishek kasana
abhishek kasana

Reputation: 1522

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

Upvotes: 1

Related Questions