Solved

why delete query must have DISTINCTROW keyword to work?

Posted on 2013-12-15
4
1,175 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 37

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

Suggested Solutions

Title # Comments Views Activity
Microsoft Access - limit entry to numbers and letters 10 63
GA Ribbon creator 9 65
Queries: Select, then Append, then Delete 8 41
Cant delete records in query 8 50
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

710 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