Source language: Translate to:

Removing a subset of a table

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Removing a subset of a table

Postby dpayer » Mon Oct 01, 2012 1:42 pm

I have a report that is generated by an app. It provides a CSV that identifies a list of machines. We need to analyze a portion of the list. The part we don't want in the analysis (which will be done at least once a day and likely more often) is a static table of exclusions which is a subset of the original table.

I delete and then import every time the newest list for the main table. I have the excluded list as a static table. Both in the same database.

I thought I could define a relationship between the tables and then delete the resultant entries from the main table. But it is not working. It deletes the whole table.

Code: Select all
dbpOpenDatabase "GPS" "C:\Users\d_payer.SGA\Documents\NeoBook 5\DBP-projects\GPS\db\GPSdata.mdb"
dbpOpenTable "GPS" "CurrentExceptions" ""
dbpOpenTable "GPS" "Exclusions" ""
alertbox "Totals" "Exclusions: [GPS.Exclusions.$RecCount]|Total records: [GPS.CurrentExceptions.$RecCount]"
dbpDefineRelationship "GPS" "Exclusions" "TerminalID" "GPS" "CurrentExceptions" "TerminalID"
dbpShowGrid "GPS" "CurrentExceptions" "Rectangle1"
dbpDeleteAll "GPS" "CurrentExceptions"
dbpEndRelationship "GPS" "Exclusions" "CurrentExceptions"
alertbox "Totals" "Exclusions: [GPS.Exclusions.$RecCount]|Total records: [GPS.CurrentExceptions.$RecCount]"


Should the defined relationship have a name for its resultant table?

What might be a better way to clean up my table from the excluded items? The main table has 2000 entries and the exclusions list has 500 entries. Do I need to loop through the exclusion list, search for that entry in the DB and then delete the record (may take a long time).

Thanks in advance for any input.

David
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Postby Neosoft Support » Mon Oct 01, 2012 2:12 pm

This is a pretty complicated SQL question, but I think the solution involves an inner join command as described here:

http://databasically.com/2011/08/31/using-sql-to-delete-rows-from-a-table-using-inner-join/
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby dpayer » Mon Oct 01, 2012 2:31 pm

Neosoft Support wrote:This is a pretty complicated SQL question, but I think the solution involves an inner join command as described here:

http://databasically.com/2011/08/31/using-sql-to-delete-rows-from-a-table-using-inner-join/


All the simple questions got asked already so I had to try something new :)

D
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Postby Gaev » Tue Oct 02, 2012 8:12 am

DavidP:

1) In your posted code, the dbpDefineRelationship command has syntax that is incompatible with that in the Help file.

2) In any case, I don't think it is the right command to use for what you are looking to do ... instead, try this ...
Code: Select all
dbpOpenDatabase "GPS" "C:\Users\d_payer.SGA\Documents\NeoBook 5\DBP-projects\GPS\db\GPSdata.mdb"
SetVar "[thisSQL]" "SELECT * FROM CurrentExceptions WHERE TerminalID IN (SELECT TerminalID FROM Exclusions)"
dbpExecSQL "GPS" "[thisSQL]" "ToBeDeleted"
dbpDeleteAll "GPS" "ToBeDeleted"

AFTER YOU BACKUP YOUR DATABASE !!!:shock:

You might also be able to "not need the dbpDeleteAll command by using DELETE * in place of SELECT *

Caveat Emptor: I have not tried this.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby dpayer » Tue Oct 02, 2012 11:08 am

Gaev wrote:DavidP:

1) In your posted code, the dbpDefineRelationship command has syntax that is incompatible with that in the Help file.

2) In any case, I don't think it is the right command to use for what you are looking to do ... instead, try this ...
Code: Select all
dbpOpenDatabase "GPS" "C:\Users\d_payer.SGA\Documents\NeoBook 5\DBP-projects\GPS\db\GPSdata.mdb"
SetVar "[thisSQL]" "SELECT * FROM CurrentExceptions WHERE TerminalID IN (SELECT TerminalID FROM Exclusions)"
dbpExecSQL "GPS" "[thisSQL]" "ToBeDeleted"
dbpDeleteAll "GPS" "ToBeDeleted"

AFTER YOU BACKUP YOUR DATABASE !!!:shock:

You might also be able to "not need the dbpDeleteAll command by using DELETE * in place of SELECT *

Caveat Emptor: I have not tried this.


Thanks Gaev, at least now I realize that the define relationship is not exactly what I needed. I also changed the statements to be 'dbpOpenAccessDatabase' as I am using that type.

Your query doesn't quite work either but the interesting point is that your select statement should generate a resultant table (the option in the SQLExec) which you named "ToBeDeleted". With that query, the app generates an error saying:
Code: Select all
The Microsoft Jet database engine cannot find the input table or query 'ToBeDeleted'. Make sure it exists and that its name is spelled correctly.


I tried both a SELECT AS to give it that name and I attempted to use the dbpOpenTable to access a table by that name to no avail.

Dave gave a resource to look at which I modified for my purpose. It falters in other ways:

Code: Select all
DELETE CurrentExceptions
FROM CurrentExceptions INNER JOIN Exclusions
ON  CurrentExceptions.RetailerID = Exclusions.RetailerID
WHERE Exclusions.RetailerID > ""


(attempting to form the relationship on the RetailerID field then choosing where any entry exists in the criteria table.)

This errors out with: "Specify the table containing the records you want to delete."

I'll post back when I find the solution.

David P.
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Postby Gaev » Tue Oct 02, 2012 12:02 pm

DavidP:

a) did you try ...
Code: Select all
SetVar "[thisSQL]" "DELETE * FROM CurrentExceptions WHERE TerminalID IN (SELECT TerminalID FROM Exclusions)"
dbpExecSQL "GPS" "[thisSQL]" ""
... no need for temporary Table.

b) also try this ...
Code: Select all
SetVar "[thisSQL]" "SELECT * INTO ToBeDeleted FROM CurrentExceptions WHERE TerminalID IN (SELECT TerminalID FROM Exclusions)"
dbpExecSQL "GPS" "[thisSQL]" ""
dbpDeleteAll "GPS" "ToBeDeleted"
... create temporary Table as part of the SELECT command (with INTO ToBeDeleted clause).

c) If all else fails, in response to ...
Code: Select all
The Microsoft Jet database engine cannot find the input table or query 'ToBeDeleted'. Make sure it exists and that its name is spelled correctly.
... try and create an empty table with this name and same fields/columns as CurrentExceptions ... before doing the SELECT.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby dpayer » Tue Oct 02, 2012 12:55 pm

Gaev,

The second query actually worked but I was using a generic method and Microsoft Access databases have a slightly different syntax.

Here is the query that got the desired results:

Code: Select all
DELETE CurrentExceptions.* FROM CurrentExceptions INNER JOIN Exclusions ON  CurrentExceptions.RetailerID = Exclusions.RetailerID WHERE Exclusions.RetailerID > ""


The table needs to be identified with a trailing [dot][star]

Thanks for your help.

David P.
User avatar
dpayer
 
Posts: 1380
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests