Solved

why delete query must have DISTINCTROW keyword to work?

Posted on 2013-12-15
4
1,095 Views
Last Modified: 2013-12-17
hey guys,

i used the QBE to write a delete query. however it could not work. i remember the solution from EE being switch on Unique Records in the QBE and it works. now i would like to understand why. when i switch on Unique Records, in SQL it just adds the keyword DISTINCTROW.

question --> why must i have DISTINCTROW for the delete query to work?

thanks in advance guys!! = ))

'this works
DELETE DISTINCTROW N.*, N.Imported
FROM WorkTime_Actual_tbl AS N INNER JOIN WorkTime_StaffAndWorkDate_Uploaded_qry AS U ON (U.WorkDate = N.WorkDate) AND (N.Staff_tblPK = U.Staff_tblPK)
WHERE (((N.Imported)=-1));

Open in new window


'this does not work
'error message --> could not delete from specififed tables
DELETE N.*, N.Imported
FROM WorkTime_Actual_tbl AS N INNER JOIN WorkTime_StaffAndWorkDate_Uploaded_qry AS U ON (N.Staff_tblPK = U.Staff_tblPK) AND (N.WorkDate = U.WorkDate)
WHERE (((N.Imported)=-1));

Open in new window

0
Comment
Question by:developingprogrammer
4 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 167 total points
Comment Utility
I'm guess that your query would return multiple rows from N as part of the join. This would confuse Access, By adding the DISTINCTROW keyword, it will bring the queryback to unique rows from N


Kelvin
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
Comment Utility
Are you sure you need the join?  is it limiting the selected rows in addition to the explicit criteria?

When you use multiple tables in a delete query, rows can ONLY be deleted from the many-side table.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 167 total points
Comment Utility
when  deleting records using join for non primary keys, you will get an  error message that states "could not delete from specified tables".

to make the delete query work, the Sql statement need the Distinctrow syntax to specify that there is a unique relationship between the two tables.

my 2¢
0
 

Author Comment

by:developingprogrammer
Comment Utility
hey Kelvin, Pat and capricorn1, thanks for all your help! = )

Kelvin that's a good point you make! i will keep that in mind thanks! = )

yup Pat it is limiting the rows in addition to the criteria = ) rows can only be delete from the many-side table. hrmmm. what do you mean by the many side table? that's an interesting point you make = )

capricorn1 that's a super good point! i think that's the whole problem --> me not having primary keys cause the query to think that the relationship is not unique. adding the distinctrow syntax solves the problem. thanks capricorn1!! = ))
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now