Potential Problems with SQL UPDATE statement - One to Many Relationship

[AlexB123]

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:

locID	RESP_REQD
123	1
456	1

In tbl_RESPONSE I have three entries (respID is my primary key, locID is the foreign key for tbl_STATUS) :

respID	locID	respFLG	fileName
77	123	0	fileA
81	456	0	fileB
85	456	0	fileC

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?

UPDATE tbl_STATUS a
INNER JOIN tbl_RESPONSE b
ON a.locID = b.locID
SET a.RESPONSE_REQD = 1
WHERE b.respFLG = 0
;

 

[jackd]

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]

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.