I was setting up foreign key of one table by using some data from other table. I am trying to do this by using for loop in node-js and the db is in postgres. There is too much data and I am not sure why all rows are updated, some are left null at the end even tough I am updating then one row at a time. I am using drizzle orm to access the db and node-postgres client.

Example Tables -

Table A - code (varchar)(PK), api_response (json), reference_to_table_b 
Table B - name (varchar)(PK)   

Now my data in table A is populated other than FK to table B. To set this foreign key I have to check some fields in api_response, then set the foreign key according to condition.

To do so I am using a loop in node.js as -

const allRows = await db.select().from(table_A);

for(let aInstance of allRows) {
   if(aInstance.api_response.fields === condition) {
       const correspondingRowInB = await db.select().from(table_B).where({condition});

       const updatedRow = await db.update(table_A).set({ updated_field }).where({ update_condition });
   }
}

Some of the things I suspected were -

1.) It may be because of async access so I have added async await to ensure that it proceeds ahead only after update is done. But still it does not work.

2.) dirty write is happening hence old value is reflected which is null. But when I wrap my looped updates in transaction with async await still the same output is seen.

Can someone guide what could be the issue?

Can someone also guide about why such loop updates are antipatterns and not good to use?