Solved

why delete query must have DISTINCTROW keyword to work?

Posted on 2013-12-15
4
1,158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 167 total points
ID: 39720922
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 36

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
ID: 39721982
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 167 total points
ID: 39722420
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
ID: 39725799
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

740 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