CODEWITHSUNDEEP
sqlloopsdatetimecursors
Paul
Paul

Reputation: 1

How to delete rows from a table based on a condition in another table?

I have a table 'ArrayTemp' which contains several columns, one of which is a timestamp. I have a query 'q_MakeTempTable' that contains a small number of rows from table A. I wish to create a table 'ToBeDeleted' that contains all rows in table 'ArrayTemp' whose timestamp is within 30 seconds of any of the timestamps in query 'q_MakeTempTable'.

I can do this in MSAccess using a recordset but am struggling to do the same in SQL. The code I use in MSAccess VBA is:

strSQL = "SELECT ArrayTemp.TimeMS, ArrayTemp.MsgProc, ArrayTemp.StateAfter, ArrayTemp.MsgClass, ArrayTemp.MsgNumber, ArrayTemp.TimeString, ArrayTemp.MsgText, Abs(\[ArrayTemp\].\[TimeMS\]-\[q_MakeTempTable\].\[TimeMS\]) AS Expr1 FROM ArrayTemp, q_MakeTempTable WHERE (((Abs(\[ArrayTemp\].\[TimeMS\]-\[q_MakeTempTable\].\[TimeMS\]))\<30))"
Set rstMessageNumber = dbRM2Alarms_V2.OpenRecordset(strSQL, dbOpenDynaset)

rstMessageNumber.MoveLast
lngRecordCount = rstMessageNumber.RecordCount
rstMessageNumber.MoveFirst

Do While Not rstMessageNumber.EOF
lngCount = lngCount + 1
DoEvents
strSQL = "INSERT INTO ToBeDeleted VALUES (" & rstMessageNumber!TimeMS & "," & rstMessageNumber!MsgProc & "," & rstMessageNumber!StateAfter & "," & rstMessageNumber!MsgClass & "," & rstMessageNumber!MsgNumber & ",'" & rstMessageNumber!TimeString & "'" & ",'" & rstMessageNumber!MsgText & "'" & ")"
dbRM2Alarms_V2.Execute strSQL, dbFailOnError
rstMessageNumber.MoveNext
Loop

Upvotes: 0

Views: 16

Answers (0)

Related Questions