0

I am converting Access to SQL and I got stuck at this Delete Statement because I am not sure what it does exactly. 

DELETE TableA.cID, TableB.*
FROM TableA RIGHT JOIN TableB ON TableA.cID = TableB.CID2
WHERE (((TableA.cID) Is Null));

I am guessing it is something like this:

Delete
from TableA right join TableB ON TableA.cID = TableB.CID2
Where TableA.cID is null

I am not really sure if it should delete from 1 table or 2 or..

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
AngelicCore
  • 1,413
  • 3
  • 22
  • 40

1 Answers1

0

You want:

Delete TableB
from TableA right join TableB ON TableA.cID = TableB.CID2
Where TableA.cID is null

Personally, I always avoid right joins because the same thing can be stated by switching the order of the tables. I would re-write the statement like this:

DELETE B
FROM TableB AS B LEFT JOIN TableA AS A ON B.CID2 = A.cID
WHERE A.cID IS NULL

See T-SQL: Selecting rows to delete via joins for further information.

As for what that query is actually doing, here is a brief description:

Delete all rows in TableB where no matching record exists in TableA (TableA.cID IS NULL) for the condition TableB.CID2 = TableA.cID

Community
  • 1
  • 1
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • But isn't the access query deleting from 2 tables at once? – AngelicCore Aug 29 '13 at 17:10
  • No it is not delete from table 2 – AB Vyas Aug 29 '13 at 17:40
  • No, the `TableA.cID` portion of `DELETE TableA.cID, TableB.*` is likely an artifact of Access's query-by-design view. The original programmer probably double-clicked the `cID` field from TableA to add that field to the query design grid (this is the part that would have added `TableA.cID` to the first line of the query), then typed `Is Null` in the Criteria row of the `TableA.cID` column of the query design grid so that **only rows from TableB with *no* counterpart in TableA** would be deleted by the query. The `TableB.*` in the first line of the query tells Access to only delete from TableB. – mwolfe02 Aug 29 '13 at 20:18