Updating col from one table to another

naveen327

New Member
Joined
Jul 17, 2007
Messages
13
Hi I have imported two different CSV files (A.csv and B.csv) into two different tables (tbl_A and tbl_B) respectively using a VBA Import function.

When I try to update a col_product (which is empty) in tbl_A with data from col_product in tbl_B on a inner join between column concat in tbl_A and col_concat in tbl_B.

My update query looks like this
UPDATE tbl_A SET tbl_A.Product = (SELECT tbl_B.Product
FROM tbl_B INNER JOIN tbl_A ON tbl_A.Concat=tbl_B.Concat);

The select subquery within the update returns 488 rows with the products but when I run the update query it shows 488 rows which are blank?

Note: Should I be telling the update query exactly which rows to update on in tbl_A? The problem with that is the primary key for both tables is an autonumber.

Any help would be much appreciated.
Thanks, Naveen
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
I created the query myself and tested it out and it seems to work. Here is the SQL that the Query Builder built that works:
Code:
UPDATE tbl_A 
INNER JOIN tbl_B 
ON tbl_A.Concat = tbl_B.Concat 
SET tbl_A.Product = [tbl_B]![Product];
 

naveen327

New Member
Joined
Jul 17, 2007
Messages
13
Hi,

Sorry for posting twice, I'll check more carefully next time also thanks for the reply your query worked well.

I realised the problem was with one of the fields i am using in the join which is not a pk in one of the tables.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top