Update & Append Query

[GC1]

Hi All,

I have a new small DB and there is table know as "1103_System", I have another import table know as Sheet1. Sheet1 is used to cleanse data then my plan is to append this data onto 1103_System.

Most of the data appends without fail with the exception of a foreign key "HierarchyID" on table "1103_System". I have tried and update query of the foreign key and no result - the foreign key already exists . I other tables where I will need to append data, perhaps someone has a solution.

I tried this and somehow it added a couple hundred blank rows onto the 1103_System -

UPDATE Sheet1, 1103_System SET Sheet1.HierarchyID = [1103_System].[HierarchyID];

Thanks for reading,
Stay Safe
GC1

 

[isladogs]

If you have the field HierarchyID set to allow no duplicates, that will prevent any duplicate records being imported.
Similarly (though not relevant here) if set as Required and the imported field value is null

 

[GC1]

Hi isladogs,

HierarchyID - Indexed to No and Required also set to no

I tried added the hiearchyID field to Sheet1 populated the value and still no joy. I used this as a trial, the pop screen referenced updating the 58 records but nothing is showing.

UPDATE 1103_System INNER JOIN Sheet1 AS Sheet1_1 ON [1103_System].SystemID = Sheet1_1.SystemID SET Sheet1_1.HierarchyID = [1103_System].[HierarchyID];

 

[The_Doc_Man]

UPDATE Sheet1, 1103_System SET Sheet1.HierarchyID = [1103_System].[HierarchyID];

Note that in UPDATE Sheet1, 1103_System SET ... you have what is called a Cartesian JOIN because it is unconstrained. I don't know exactly why it did what it did but that is certainly the wrong syntax for anything practical.