-
Feb 19th, 2021, 04:25 PM
#1
Thread Starter
Fanatic Member
[RESOLVED] Need help with this complicated query
Hello vbforums
I have 3 tables as shown below
I want to delete all records that are in both tbl1 and tbl3 but do not exist in tbl2
this is what I'm doing but no success.
Code:
StrSql = "Select * From Tbl1 inner join Tbl2 on Tbl1.Id = Tbl2.PID " & _
" inner join Tbl3 on Tbl1.ID = Tbl3.SID" & _
" where pass = 'Non' and PID <> SID "
Set Rs = Cnn.OpenRecordset(StrSql)
If Not Rs.EOF Then
Do While Not Rs.EOF
Rs.Delete
Rs.MoveNext
Loop
End If
End If
In this scenarion I expect this query to delete Record 4 in Tbl1 and Tbl3.
thank you
-
Feb 19th, 2021, 05:00 PM
#2
Re: Need help with this complicated query
This?
Code:
select * from T1 inner Join T3 on T1.id = T3.sid where T1.id not in (select T2.pid from T2)
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Feb 19th, 2021, 05:42 PM
#3
Thread Starter
Fanatic Member
Re: Need help with this complicated query
Originally Posted by sapator
This?
Code:
select * from T1 inner Join T3 on T1.id = T3.sid where T1.id not in (select T2.pid from T2)
thank you very much
It worked to sort out the record
But it fails to delete.
Code:
If Not Rs.EOF Then
Do While Not Rs.EOF
Rs.Delete
Rs.MoveNext
Loop
End If
-
Feb 19th, 2021, 06:52 PM
#4
Re: Need help with this complicated query
Perhaps this will do the trick?
Code:
Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl3 ON Tbl1.ID = Tbl3.SID WHERE Tbl1.ID NOT IN (SELECT Tbl2.PID FROM Tbl2))"
-
Feb 19th, 2021, 06:54 PM
#5
Re: Need help with this complicated query
Originally Posted by jpbro
Perhaps this will do the trick?
Code:
Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl3 ON Tbl1.ID = Tbl3.SID WHERE Tbl1.ID NOT IN (SELECT Tbl2.PID FROM Tbl2))"
I had that exact same post and then deleted it because it appears he wants to delete one at a time from the client (is that ADO?)
-
Feb 19th, 2021, 07:53 PM
#6
Thread Starter
Fanatic Member
Re: Need help with this complicated query
Originally Posted by jpbro
Perhaps this will do the trick?
Code:
Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl3 ON Tbl1.ID = Tbl3.SID WHERE Tbl1.ID NOT IN (SELECT Tbl2.PID FROM Tbl2))"
Big progress but still I can't find my happiness because I can only delete from one table0
thank you
-
Feb 19th, 2021, 07:55 PM
#7
Thread Starter
Fanatic Member
Re: Need help with this complicated query
Originally Posted by szlamany
(is that ADO?)
Sqlite and RC6 but The same approach as ADO.
-
Feb 19th, 2021, 08:23 PM
#8
Re: Need help with this complicated query
I don't think there's any advantage to deleting records in a loop vs. via an SQL statement. However, if you do want to use a loop for some reason, I think you might need to travel in reverse. E.g.:
Code:
If Rs.Recordcount Then
Rs.MoveLast
Do Until Rs.BOF
Rs.Delete
Rs.MovePrevious
Loop
End If
-
Feb 19th, 2021, 11:30 PM
#9
Re: Need help with this complicated query
Perhaps you could do it like this, delete the records from the 2 tables while traversing the records. Hand-coded, please check for any syntax error. Another alternative is you could CASCADE the delete from tbl1 to tbl3 such that if one record is deleted from tbl1 then it's corresponding record from tbl3 is also deleted. I am just not sure if this is possible with sqlite as I haven't used it.
Code:
If Not Rs.EOF Then
Do While Not Rs.EOF
'Rs.Delete
Cnn.Execute "DELETE FROM Tbl1 WHERE ID = " & rs.fields("ID") & ""
Cnn.Execute "DELETE FROM Tbl3 WHERE ID = " & rs.fields("ID") & ""
Rs.MoveNext
Loop
End If
-
Feb 20th, 2021, 05:53 AM
#10
Thread Starter
Fanatic Member
Re: Need help with this complicated query
Thank you all
I think I found a workaround
Code:
Cnn.Execute "DELETE FROM Tbl1 WHERE ID IN (SELECT ID FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.ID = Tbl2.PID " & _
" WHERE Tbl1.ID NOT IN (SELECT Tbl3.SID FROM Tbl3))"
Cnn.Execute "DELETE FROM Tbl2 " & _
" WHERE Tbl2.PID NOT IN (SELECT Tbl1.ID FROM Tbl1)"
But I have a question out of curiosity.
This code deletes from Tbl1 first then Tbl2.
If I try to do that manually, I get message error, foreing key constraint.
To be able to delete a record manually, I must delete first from Tbl2.
thank you
-
Feb 22nd, 2021, 02:05 AM
#11
Re: Need help with this complicated query
Yes, if you have FK-Constraints, you have to delete in reverse Order, since the constraint won't allow a child-record being without a parent.
The more interesting question: What kind of constraints do you have on your Foreign Key?
If you have a DELETE ON CASCADE, then you just need to delete the parent-record, and the DBMS takes care of deleting the child.
No need to delete from the child-table (that's probably, why your "manual" delete fails)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 22nd, 2021, 03:14 PM
#12
Thread Starter
Fanatic Member
Re: [RESOLVED] Need help with this complicated query
Code:
If you have a DELETE ON CASCADE, then you just need to delete the parent-record, and the DBMS takes care of deleting the child.
that's it thank you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|