Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

why delete query must have DISTINCTROW keyword to work?

Posted on 2013-12-15
4
Medium Priority
?
1,248 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 668 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 39

Assisted Solution

by:PatHartman
PatHartman earned 664 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 668 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

719 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