• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1378
  • Last Modified:

why delete query must have DISTINCTROW keyword to work?

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
developingprogrammer
Asked:
developingprogrammer
3 Solutions
 
Kelvin SparksCommented:
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
 
PatHartmanCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
developingprogrammerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now