How do I update fields from one table with values from another table

Question

I'm attempting to update two fields in one table, with values from two fields in a second table, without a primary key to link the two tables.

The second table doesn't have a primary key because it's created via a Transfersheet method just to Import data into it from Excel.

Here's my code so far (currently this code doesn't do anything):

 SQL = "MERGE TableA AS Target" & _
      " USING TableB AS Source" & _
      " ON " & _
      "(Target.Field1=Source.Field1,Target.Field2=Source.Field2)" & _
      " WHEN NOT MATCHED BY Target" & _
      " THEN UPDATE (Field1,Field2)" & _
      " VALUES (Source.Field1,Source.Field2)"

Possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Kevin Hogg, Mar 01 '16 at 12:26
problem is that Update doesn't do nothing. I have tested with : In Excel worksheet one of Field1 has value "1111" and Field2 value "Microsoft", that is one row. In my DB only field2 ("Microsoft") is entered, Field1 is blank. It want to insert value "1111" in Field1. That's It. — LuckyLuke82, Mar 01 '16 at 12:29
@KevinHogg, I don't have ID in second table, I allready mentioned that. I'm doing Update from Excel spreadsheet - this sheet's data is imported viy TransferSheet method into new table, and Access doesn't create ID number field. see my edited question for TrasferSheet method code, It executes before SQL. — LuckyLuke82, Mar 01 '16 at 12:31
First delete TableA.Field1 = tableB.Field1 ... you are doing join via those, they are already same as they are joining, 2nd are you sure columns in tables are the same type? which is that? nvarchar? int? 3rd toss in some input please so we can actually point you toward right direction — Veljko89, Mar 01 '16 at 12:37
@Veljko, great point. When Access creates TableB, both fields are Text Numbers, but one of them should be Number. I created TableB on my own, so now TransferSheet method Imports into existing table, with Number field. So, Field1 is Number, Field 2 is Text, in both tables. I hav edited my code, see above, is that now correct ? — LuckyLuke82, Mar 01 '16 at 12:56
@LuckyLuke82 Please note that the possible duplicate link is an example of how to update one table from another, and whilst it uses an identifier for the join you can join on anything, including non-identifiers. Are you saying that your update statement doesn't work because the data in `TableB.Field1` is empty, and you want to set `TableA.Field1 = 1111`? — Kevin Hogg, Mar 01 '16 at 13:03
TableB.field1 is "1111", and TableA.field is blank. Update is doing from TableB to TableA — LuckyLuke82, Mar 01 '16 at 13:10
Personally, in this type of situations, i just create physical table and import whole excel into it ... and then just make stored procedure that does update ... that way you have bigger control over your data flow and manipulation. Try it that way, make another table, fix your code that does full import on new table and then have procedure that deals with your update. if you can't, at this moment, go with instant update just break problem in little pieces and deal with one at the time — Veljko89, Mar 01 '16 at 13:23
@Veljko89, I actually have done that - created psysical table to Import whole Excel data. I also have stored procedure - Import without duplicates to destination table. Unfortunally this Import doesn't solve existing rows in destination table that have one of the fields empty. And I'm breaking problems in little pieces, but this peaces have become a big cake of problems by now;) — LuckyLuke82, Mar 01 '16 at 20:17
@Veljko and all others , please check my updated question, I tried Merge but It's not working - error 3078 " Access Database engine cannot find input table or query. Make sure It exists and that It's name is spelled correctly". However, table does exist, and spelling is double checked - no mistakes here. What's wrong ?? — LuckyLuke82, Mar 01 '16 at 21:20
@HansUp, thanks for that. So I'm left only with JOIN or UPDATE ? — LuckyLuke82, Mar 02 '16 at 06:27
@Hans thanks for info, but I have allready managed to solve this problem. I will post answer in a few minutes. — LuckyLuke82, Mar 02 '16 at 16:47

score 0 · Answer 1 · answered Mar 01 '16 at 12:50

0

Use MERGE for that. https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Syntax:

MERGE  [AS TARGET]
USING  [AS SOURCE]
ON 
[WHEN MATCHED 
THEN  ]
[WHEN NOT MATCHED [BY TARGET]
THEN  ]
[WHEN NOT MATCHED BY SOURCE
THEN  ];

You don't necessarily have to declare all the possible outcomes of the comparison. Also you can define the same action on all of them. I'm not sure what you want to achieve in terms of match.

answered Mar 01 '16 at 12:50

fishmong3r

1,414
4
24
51

can you edit this into my example, not sure on how should I build Merge ? – LuckyLuke82 Mar 01 '16 at 12:58
I think you are on the wrong site. The purpose of this community is not to do each others homework, but to give some idea/clue/starting point. Please read the documentation of `MERGE` carefully, and I'm sure you'll find the way. – fishmong3r Mar 01 '16 at 13:01
I'll try, It wasn't meant like you were doing my homework. But explanation is a little bit hard for beginner like me.Thanks for provided link. – LuckyLuke82 Mar 01 '16 at 13:05
Could you please check my updated question, I tried following instructions from your provided link, but MERGE is not working in Access. – LuckyLuke82 Mar 01 '16 at 21:21

score 0 · Accepted Answer · answered Mar 02 '16 at 16:55

I have finally managed to solve this issue. This is the only synthax that worked for me:

SQL = " UPDATE TableA" & _
      " INNER JOIN TableB" & _
      " ON TableB.Field1=TableA.Field1 OR" & _                                                 " TableB.Field2=TableA.Field2" & _
      " SET TableA.Field1=TableB.Field1, TableA.Field2=TableB.Field2"

This statement successfully updates both fields If one of them is empty in target table and not in source table.

Thanks for all your efforts, I hope this will come useful to someone, Access is quite a hard rock when It comes coding SQL in VBA.

How do I update fields from one table with values from another table

2 Answers2