Need Serious Guru SQL Database Query Help

I need to find a way using the table scenario below to remove THE ROWS if the Store_Id and Customer_Id is the same but the Vendor_id is different. (Do not remove if Vendor_ID is the same)using SQl CODE. This list could go on an on in the table with various different number's and stores, etc.


(identity)
number_id Store_Id Vendor_Id Customer_Id Quantity
--------- | --------- | --------- | ----------- | --------
1 | 100 | 444 | 0 | 100
2 | 100 | 444 | 0 | 200
3 | 100 | 555 | 2 | 50
4 | 200 | 666 | 0 | 125
5 | 100 | 888 | 0 | 21

So essentially I would need the remove the last column using in this example using the logic from above.

I would be sincerely grateful for any guru coding examples/help!!!



[Edited by Bebe on 09-28-2000 at 12:36 AM]

DELETE FROM [TABLE_NAME]
WHERE (CustomerID = StoreID)
AND (CustomerID <> VendorID)

Is this what you need?

What you're looking for is something like this. The first part is just a standard procedure I use for locating/opening the database. Locations are stored in the INI files.
The second part just scrolls through the recordset checking the criteria. I presume you want to delete the entire row given the criteria is met.

Hope this helps.

Bob





Dim Duplicates As Recordset

Dim pth, sys As String
Open "<net path>\database.ini" For Input As #1
Open "<net path>\system.ini" For Input As #2
Input #1, pth
Input #2, sys
Close #1
Close #2
dbPath = pth
sysPath = sys
DBEngine.SystemDB = sysPath
DBEngine.DefaultUser = "<USERNAME>"
DBEngine.DefaultPassword = "<PASSWORD>"

Set dbs = OpenDatabase(dbPath)





Set Duplicates = dbs.OpenRecordset("SELECT * FROM <table name>;")
Duplicates.MoveFirst

Do Until Duplicates.EOF

StoreID = Duplicates![Store ID]
CustomerID = Duplicates![Customer ID]
VendorID = Duplicates![Vendor ID]

If StoreID = CustomerID Then
If StoreID <> VendorID Then
dbs.Execute "DELETE * from <table name> WHERE [Store ID]=#" & StoreID & "# AND [Customer ID] =#" & CustomerID & "# AND [Store ID]=#" & StoreID & "#;"
End If
End If

Duplicates.MoveNext
Loop

I think my way is much simpler and quicker if executed as an in-line SQL statement.

Absolutely ! I agree, but where's the fun in that.
Bob

I didn't mean to be a kill joy!

this does not work:

DELETE FROM [TABLE_NAME]
WHERE (CustomerID = StoreID)
AND (CustomerID <> VendorID)

as customerid = 100, 200, etc and storeid = 0, 1, 2 are never the same
I need to compare different rows to different rows

the row that should be deleted from my example is the last one.

Sorry, I misunderstood what you were trying to do. Yes that is not a problem (2 SQL statements required) - but a much slower operation, as everytime you read a record you must compare it to all the unread records in the table.
I'm starting a new job this morning - but I'll be back to you in the afternooon.


Bob

What do you mean by this? You just want to delete a record that is unique for all the occurences that the store id and the customer id is the same and the vendore id is the same. I think that you should explain what you want to happen first...maybe the real world problem first. Before we go to the SQL.

I also misunderstood what you required.

I think I understand what you require now but there is one concern I have:

Which rows that get deleted will depend on which order the rows are stored in the table. (or the order they're retrieved).

What I mean is, in your example, if the rows were in reverse order, the last two rows would be deleted right?

If I'm wrong then I've misunderstood your needs again and I'm sorry. Otherwise, are you sure?

Bebe,
Sorry I couldn't get back to you sooner. This works, and I think it is what you're looking for. You might let me know.
Bob
_________________________________________

Private Sub Form_Load()
On Error GoTo Err:

Dim recs As Recordset
Dim duplicates As Recordset
Dim aray()
Dim i As Integer

Set recs = dbs.OpenRecordset("SELECT * FROM xxx_test")
Set duplicates = dbs.OpenRecordset("SELECT * FROM xxx_test")
recs.MoveLast
recscount = recs.RecordCount

ReDim aray(1 To recscount - 1)
recs.MoveFirst


Do Until recs.EOF
SID = recs![Store_ID]
CID = recs![Customer_ID]
VID = recs![Vendor_ID]

duplicates.MoveFirst
i = 1
Do Until duplicates.EOF
If (CID = duplicates![Customer_ID]) And (CID = duplicates![Store_ID]) And (CID <> duplicates![Vendor_ID]) Then
aray(i) = duplicates![Number_ID]
i = i + 1
End If
duplicates.MoveNext
Loop

If i > 1 Then
For j = 1 To i - 1
SQLDel = "DELETE * FROM xxx_test WHERE [Number_ID] =" & aray(j) & ""
dbs.Execute SQLDel
msg = MsgBox("record deleted")
Next
duplicates.Requery
End If

recs.MoveNext

Loop

dbs.Close
Err:
If Err.Number = 3167 Then
Resume Next
End If

End Sub
_______________________________________

this is great is there a way to do all this just within sql????