SQL DELETE with JOIN

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to perform a SQL DELETE with a JOIN on an ACCESS database but
have not been able to.
The documentation is not clear as to whether the DELETE instrucion supports
JOINS or not

Its a 2 Table problem.

'The following CommandText doesn't work

'.CommandText = "DELETE FROM TestMaterials " & _
"INNER JOIN Materials " & _
"ON Materials.MaterialID = TestMaterials.MaterialID " & _
"WHERE ((TestMaterials.ProgID = '" & SelectedProgID & "') " & _
"AND (Materials.MatName = '" & mtMaterialOrgCellValue & "'))"


By splitting the operation into 2 parts, it works

.CommandText = "SELECT MaterialID FROM Materials " & _
"WHERE MatName = '" & mtMaterialOrgCellValue & "'"

Set objRS = New ADODB.Recordset
Call OpenDisconnectedRecordset(strConnection, objRS, objCom)

With objRS
If Not .EOF Then
If Not IsNull(.Fields.Item(0).Value) Then
tMatID = CStr(.Fields.Item(0).Value)
End If
End If
End With

.CommandText = "DELETE FROM TestMaterials " & _
"WHERE ((ProgID = '" & SelectedProgID & "') " & _
"AND (MaterialID = " & tMatID & "))"
 
You must tell the database which from which table you want the rows deleted.
If this is an Access database, you must also include the DISTINCTROW
keyword. Like this:

'.CommandText = "DELETE DISTINCTROW TestMaterials.* " & _
"FROM TestMaterials " & _
"INNER JOIN Materials " & _
"ON Materials.MaterialID = TestMaterials.MaterialID " & _
"WHERE ((TestMaterials.ProgID = '" & SelectedProgID & "') " & _
"AND (Materials.MatName = '" & mtMaterialOrgCellValue & "'))"

You can also solve this without the JOIN:

'.CommandText = "DELETE TestMaterials.* " & _
"FROM TestMaterials " & _
"WHERE ((TestMaterials.ProgID = '" & SelectedProgID & "') " & _
"MaterialID IN (SELECT MaterialID FROM Materials " & _
"WHERE (Materials.MatName = '" & mtMaterialOrgCellValue & "'))"

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Brilliant

Thanks John

Even though I'd been searching through many references and example queries,
I hadn't appreciated that the DELETE statement can be applied in the same way
as the SELECT statement.

Much appreciated

Tinius
 
Back
Top