Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update Query Error: Single-Row update/delete affected more than one row of a linked table.

Status
Not open for further replies.

Denae

Instructor
Apr 15, 2016
29
US
I have an access database that has linked SQL tables. One of the tables (dbo_EmployeeInformation) has employee records that occasionally need to be updated. I have an extract of current data that I have tried linking into the database as well as creating a table in the database where I paste the information.

I don't want to replace the entire content of dbo_EmployeeInformation, because I would loose historical data for employees that have left the company, I just need to compare the new data with the old to see if anything changed (Name, Address, Manager, etc) and update dbo_EmployeeInformation with these changes.

I created an update query that relates the two tables on their primary keys:
[dbo_EmployeeInformation].[strEmployeeID] = [tblEmployeeInformation_Update].[Employee ID]​


When I run the query I get the error:
Single-Row update/delete affected more than one row of the linked table. Unique index contains duplicate values.
I have checked both tables and there are no duplicates in the primary key fields.

Here is the query I am using:

Code:
UPDATE dbo_EmployeeInformation INNER JOIN tblEmployeeInformation_Update ON dbo_EmployeeInformation.strEmployeeID = tblEmployeeInformation_Update.[Employee ID] SET dbo_EmployeeInformation.strActiveStatus = [tblEmployeeInformation_Update].[Active Status], dbo_EmployeeInformation.strOnLeave = [tblEmployeeInformation_Update].[On Leave], dbo_EmployeeInformation.strEmployeeName = [tblEmployeeInformation_Update].[Employee Name], dbo_EmployeeInformation.strFirstName = [tblEmployeeInformation_Update].[First Name], dbo_EmployeeInformation.strLastName = [tblEmployeeInformation_Update].[Last Name], dbo_EmployeeInformation.strMiddleName = [tblEmployeeInformation_Update].[Middle Name], dbo_EmployeeInformation.strPrimaryAddressFull = [tblEmployeeInformation_Update].[Primary Address - Full], dbo_EmployeeInformation.strAddress1 = [tblEmployeeInformation_Update].[Address 1], dbo_EmployeeInformation.strAddress2 = [tblEmployeeInformation_Update].[Address 2], dbo_EmployeeInformation.strHomePhone = [tblEmployeeInformation_Update].[Home Phone], dbo_EmployeeInformation.strJobTitle = [tblEmployeeInformation_Update].[Job Title], dbo_EmployeeInformation.strLocationCode = [tblEmployeeInformation_Update].[Location Code], dbo_EmployeeInformation.strLocationName = [tblEmployeeInformation_Update].[Location Name], dbo_EmployeeInformation.strDirectManager = [tblEmployeeInformation_Update].[Direct Manager], dbo_EmployeeInformation.strHRBusinessPartner = [tblEmployeeInformation_Update].[HR Business Partner];

Any help would be greatly appreciated.
 
Yes, both tables have the Employee ID fields designated as primary.
 
Aside from your issue, your UPDATE query looks 'dangerous' (to me) - there is no WHERE part to it, so if you run it, you will update ALL records in your dbo_EmployeeInformation table with the same information.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top