Potential Problems with SQL UPDATE statement - One to Many Relationship

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
201
Hello everyone,

Part of the functionality of my DB is to track my requests to different colleagues. I have a table of locations whose orders I need to verify. In some instances locations will be verified with a single file, and in other instances the locations will be verified with several files. I have a SQL Query that will set a flag on my table of locations to notify me that I am waiting on a colleague to respond.

For example:
Suppose I have two locations in tbl_STATUS( locID, RESP_REQD) and get the following result from my query:
locIDRESP_REQD
1231
4561


In tbl_RESPONSE I have three entries (respID is my primary key, locID is the foreign key for tbl_STATUS) :
respIDlocIDrespFLGfileName
771230fileA
814560fileB
854560fileC


Even though only two rows are updated to a RESP_REQD flag in tbl_STATUS, the system warns me I am updating three rows. Will this cause problems down the road? Is there a way to force DISTINCT Values in my Update Statement?
SQL:
UPDATE tbl_STATUS a
INNER JOIN tbl_RESPONSE b
ON a.locID = b.locID
SET a.RESPONSE_REQD = 1
WHERE b.respFLG = 0
;
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,259
Office Version
365
Will this cause problems down the road?
What is the meaning/interpretation of the 2 - 456 locID records? I see 1 is fileB, the other fileC, but we don't know your application nor business rules.
Are you requesting a response because of a particular File at that locID?
Does your application have a means to identify when requests have been resolved? That is, can you identify which record at locID 456 is the target of the request? Perhaps there is more logi involved than just the locID?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
Even though only two rows are updated to a RESP_REQD flag in tbl_STATUS, the system warns me I am updating three rows. Will this cause problems down the road? Is there a way to force DISTINCT Values in my Update Statement?
looks to me like three rows would be updated, and the system warning is correct.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,729
Messages
5,446,176
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top