Potential Problems with SQL UPDATE statement - One to Many Relationship

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
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
;
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,326
Office Version
  1. 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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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,132,900
Messages
5,655,863
Members
418,249
Latest member
JOYADA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top