Update TableA from TableB Match

1
votes

I've got two tables, the first column match on both tables and I need to UPDATE TableA with all the data from TableB, ONLY where TableA.Column1 and TableB.Column1 match. I'm having a really hard time wrapping my head around how to get this to work... TableA has about 80+ columns and TableB has around 100+. I'm attempting this in Microsoft Access.

TableA

+---------+---------+---------+---------+---------+---------+
| Column1 | Column2 | Column3 | Column4 | ....... | ColumnX |
+---------+---------+---------+---------+---------+---------+
|    1    |  DataA  |         |  DataA  | ....... |         |
+---------+---------+---------+---------+---------+---------+
|    2    |  DataA  |         |  DataA  | ....... |         |
+---------+---------+---------+---------+---------+---------+
|    3    |  DataA  |         |  DataA  | ....... |         |
+---------+---------+---------+---------+---------+---------+
|    4    |  DataA  |         |  DataA  | ....... |         |
+---------+---------+---------+---------+---------+---------+
| ....... | ....... | ....... | ....... | ....... | ....... |
+---------+---------+---------+---------+---------+---------+
|    X    |  DataA  |         |  DataA  | ....... |         |
+---------+---------+---------+---------+---------+---------+

TableB

+---------+---------+---------+---------+---------+---------+
| Column1 | Column2 | Column3 | Column4 | ....... | ColumnX |
+---------+---------+---------+---------+---------+---------+
|    1    |         |  DataB  |         | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
|    2    |         |  DataB  |         | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
|    3    |         |  DataB  |         | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
|    4    |         |  DataB  |         | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
| ....... | ....... | ....... | ....... | ....... | ....... |
+---------+---------+---------+---------+---------+---------+
|    X    |         |  DataB  |         | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+

End Result of TableA

+---------+---------+---------+---------+---------+---------+
| Column1 | Column2 | Column3 | Column4 | ....... | ColumnX |
+---------+---------+---------+---------+---------+---------+
|    1    |  DataA  |  DataB  |  DataA  | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
|    2    |  DataA  |  DataB  |  DataA  | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
|    3    |  DataA  |  DataB  |  DataA  | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
|    4    |  DataA  |  DataB  |  DataA  | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+
| ....... | ....... | ....... | ....... | ....... | ....... |
+---------+---------+---------+---------+---------+---------+
|    X    |  DataA  |  DataB  |  DataA  | ....... |  DataB  |
+---------+---------+---------+---------+---------+---------+

What I've Tried so far...

UNION which I quickly gave up on... It seems as that was only giving me a query, where as I need to actually UPDATE TableA with ALL the data from TableB

And also...

UPDATE TableA ([Column1],[Column2],[Column3],[Column4], ....... [ColumnX])
SELECT [Column1],[Column2],[Column3],[Column4], ....... [ColumnX]
  FROM TableB
 WHERE (TableA.Column1 = TableB.Column2)

EDIT 1:

Tried this...

Run-time error '3144': Syntax error in UPDATE statement

SQL = "   UPDATE tbleVendorData AS A " & _
      "     JOIN tbleOLD AS B " & _
      "       ON A.[Column1] = B.[Column1] " & _
      "      SET A.[Column14] = CONCAT(A.[Column14], B.[Column5])"

EDIT 2:

enter image description here

3
sqlms-accessvba
What's the error message? It should say approx where the error lies. - Nick
Oh, it might be you need to do A.[Column14] + B.[Column5] rather than CONCAT. I'm a native MySQL speaker! - Nick
Changed it from SET A.[Column14] = CONCAT(A.[Column14], B.[Column5]) to SET A.[Column14] = (A.[Column14] + B.[Column5]) and I'm still getting the same Syntax error - Maldred
I've updated my answer to include the horrible MSSQL syntax. - Nick
Try taking out the CONCAT, I think MSSQL only has the + operator. See my updated answer. Ooops, I'd left it in! Sorry. Updated it now. I'm too old for multi-tasking. - Nick

3 Answers

0
votes

Following query may work:

UPDATE TableA 
INNER JOIN TableB 
 ON(TableA.Column1 = TableB.Column2)
SET 
    TableA.Column3 = TableB.column3,
    ...
    TableA.Columnx = TableB.columnx
Where TableA.Column1 = TableB.column1;

Hope it helps!

0
votes

Have a play with:

UPDATE TableA AS A JOIN TableB AS B ON A.Column1 = B.Column1 SET A.Column1 = CONCAT(A.Column1,B.Column1) ...

Or in MSSQL

UPDATE A SET A.[Column14] = A.[Column14] + B.[Column5] FROM tbleVendorData AS A JOIN tbleOLD AS B ON A.[Column1] = B.[Column1]

If the value for any given field is blank in one table when it isn't in the corresponding field in the second, then you could try CONCAT(A.Column1,B.Column1) for example. It's not clever, but it is easier than using IF.

0
votes

Unfortunately none of these suggestions work... However after doing some deep digging the below worked for me!

UPDATE TableA, TableB
   SET TableA.Column15= [TableB].[Column3]
 WHERE (([TableA].[Column1]=[TableB].[Column1]));