Home > Software design >  Manipulate MS Excel tables Based on foreign Keys similar to SQL Inner-Join
Manipulate MS Excel tables Based on foreign Keys similar to SQL Inner-Join

Time:09-20

We are in the process of converting a SQL Server process to Postgres. In the interim, we want to use MS Excel to manually update the tables and load to Postgres. The SQL Server process does an inner-join on two tables to match the foreign keys and initialize certain columns based on those fields.

Is there a way to simulate this process in MS Excel? I have researched some Excel functions such as VLOOKUP and INDEX MATCH but they fall a little short on getting the job done.

The SQL query is as follows:

UPDATE table1 
SET OrderCount = 0,   
    Cost = 0.  
FROM table1.  
INNER JOIN table2 ON table2.OrderId = table1.orderID 
WHERE table2.shippingChoice = 1  

enter image description here

CodePudding user response:

If Table2 is distinct, then I think the following would work. But you'll want to check and confirm.

In picture below, I'm adding Column D and E as new columns.

Cell D3 formula:

=IF(VLOOKUP($A3,$G$3:$H$9,2) = 1,0,B3)

Drag is across and down, and you're done. If the shipping choice is 1, then use 0's, otherwise use the values.

enter image description here

  • Related