Delete Records (1 Viewer)

[MsLady]

This works perfectly.

SELECT DOCSADM_PROFILE.SYSTEM_ID, DOCSADM_PROFILE.AUTHOR, DOCSADM_SECURITY.THING, DOCSADM_SECURITY.PERSONORGROUP, DOCSADM_SECURITY.ACCESSRIGHTS
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID = DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));

Now how do i tell this query to delete these selected records from security table....?

 

[MsLady]

With this, i get the error message:
"Specify the table you want to delete from".... please help.
I only want to delete the corresponding records from the security table.

DELETE DOCSADM_PROFILE.AUTHOR, DOCSADM_SECURITY.THING, DOCSADM_SECURITY.PERSONORGROUP, DOCSADM_SECURITY.ACCESSRIGHTS
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID = DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));

thanks.

 

[Jon K]

I only want to delete the corresponding records from the security table.

Try changing the query to:-

DELETE DISTINCTROW DOCSADM_SECURITY.*
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID=DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));
.

 

[KeithG]

You are including fields from two different tables in your Delete Clause

 

[MsLady]

Try changing the query to:-

DELETE DISTINCTROW DOCSADM_SECURITY.*
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID=DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));
.

you rock!! thanks

 

[MsLady]

oh waitaminute:
this runs on access butnot on sqlserver...why?

DELETE DISTINCTROW DOCSADM.SECURITY.*
FROM DOCSADM.PROFILE INNER JOIN DOCSADM.SECURITY ON DOCSADM.PROFILE.SYSTEM_ID=DOCSADM.SECURITY.THING
WHERE DOCSADM.PROFILE.AUTHOR=21941909 AND DOCSADM.SECURITY.PERSONORGROUP=4038

Line1: Incorrect syntax near 'DOCSADM'.

and when i take out distinctrow i get:

Line1: Incorrect syntax near '*'.

please help

 

[Jon K]

Try using a subquery instead of a join.

DELETE *
FROM DOCSADM_SECURITY
WHERE PERSONORGROUP=4038 AND THING IN (SELECT SYSTEM_ID FROM DOCSADM_PROFILE WHERE AUTHOR=21941909);


If it doesn't work, try posting your question on the SQL Server forum.
.