Update & Append Query (1 Viewer)

GC1

New member
Local time
Today, 09:17
Joined
Apr 6, 2020
Messages
17
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 :confused:. 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

MVP / VIP
Local time
Today, 09:17
Joined
Jan 14, 2017
Messages
18,186
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

New member
Local time
Today, 09:17
Joined
Apr 6, 2020
Messages
17
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

Immoderate Moderator
Staff member
Local time
Today, 04:17
Joined
Feb 28, 2001
Messages
27,001
Code:
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.
 

Users who are viewing this thread

Top Bottom