SQL DELETE with JOIN

T

Tinius

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 & "))"
 
J

John Viescas

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)
 
T

Tinius

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
 
Top