|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
delete records from one table if not in anotherhow can i delete records from one table if the records do not exist in a
matching table? i am making a backup of a table before an import is performed. the users may need to undo the backup so i need to be able to delete only the new records. this is not working, but you get the idea: DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON tblPatient.PatientID = tblPatientBackup.PatientID WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; tia, mcnewxp Try creating an unmatched query and changing it to a delete query. There's a
wixard for creating an unmatched query by going to queries in the database window and clicking new. Steve Show quoteHide quote "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... > how can i delete records from one table if the records do not exist in a > matching table? i am making a backup of a table before an import is > performed. the users may need to undo the backup so i need to be able to > delete only the new records. this is not working, but you get the idea: > > DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON > tblPatient.PatientID = tblPatientBackup.PatientID > WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; > > tia, > mcnewxp > "Steve" <nonse***@nomsense.com> wrote in message good idea.news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... > Try creating an unmatched query and changing it to a delete query. There's > a wixard for creating an unmatched query by going to queries in the > database window and clicking new. > except when i do this no fields show up in the list....??? Show quoteHide quote > > > "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message > news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >> how can i delete records from one table if the records do not exist in a >> matching table? i am making a backup of a table before an import is >> performed. the users may need to undo the backup so i need to be able to >> delete only the new records. this is not working, but you get the idea: >> >> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >> tblPatient.PatientID = tblPatientBackup.PatientID >> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >> >> tia, >> mcnewxp >> > > Well, if you have imported then into tblPatient then they are in the table.
So an unmatched query is not going to show you anything. STEP 1: BACKUP your data before attempting the following. STEP 2: BACKUP your data before attempting the following. Without a backup you cannot restore the data if this does not work the way you expect. Here are four options - choose your favorite Option 1: DELETE FROM TblPatient WHERE tblPatient.PatientID in (SELECT PatientID FROM ImportSourceTable) Option 2: Optionally if you want to use the backup table instead of the import table. DELETE FROM tblPatient WHERE tblPatient.PatientID IN (SELECT Temp.PatientID FROM tblPatient as TEMP LEFT JOIN tblPatient_BU ON Temp.PatientID = tblPatient_BU.PatientID WHERE TblPatient_BU.PatientID is NULL) Option 3: On the other hand, you might just be able to replace TblPatient with TblPatient_BU. Unless other changes have been made to the records you want to retain, this would seem to be the easiest solution. Option 4: Another option would be to add a date field (DateCreated) to tblPatient and set its default to NOW(). Then all you have to do is identify records that were created at a certain date and time and delete them. DELETE FROM tblPatient WHERE DateCreated Between #2008-12-18 13:59:00# and #2008-12-18 14:00:00# John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County mcnewsxp wrote: Show quoteHide quote > "Steve" <nonse***@nomsense.com> wrote in message > news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... >> Try creating an unmatched query and changing it to a delete query. There's >> a wixard for creating an unmatched query by going to queries in the >> database window and clicking new. >> > > good idea. > except when i do this no fields show up in the list....??? > >> >> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >> news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >>> how can i delete records from one table if the records do not exist in a >>> matching table? i am making a backup of a table before an import is >>> performed. the users may need to undo the backup so i need to be able to >>> delete only the new records. this is not working, but you get the idea: >>> >>> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >>> tblPatient.PatientID = tblPatientBackup.PatientID >>> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >>> >>> tia, >>> mcnewxp >>> >> > >
Show quote
Hide quote
"John Spencer" <spen***@chpdm.edu> wrote in message the date idea is a good one.news:O0y3sxTYJHA.1272@TK2MSFTNGP04.phx.gbl... > Well, if you have imported then into tblPatient then they are in the > table. So an unmatched query is not going to show you anything. > > STEP 1: BACKUP your data before attempting the following. > STEP 2: BACKUP your data before attempting the following. > > Without a backup you cannot restore the data if this does not work the way > you expect. Here are four options - choose your favorite > > Option 1: > DELETE > FROM TblPatient > WHERE tblPatient.PatientID in > (SELECT PatientID FROM ImportSourceTable) > > Option 2: Optionally if you want to use the backup table instead of the > import table. > DELETE > FROM tblPatient > WHERE tblPatient.PatientID IN > (SELECT Temp.PatientID > FROM tblPatient as TEMP LEFT JOIN tblPatient_BU > ON Temp.PatientID = tblPatient_BU.PatientID > WHERE TblPatient_BU.PatientID is NULL) > > Option 3: > On the other hand, you might just be able to replace TblPatient with > TblPatient_BU. Unless other changes have been made to the records you want > to retain, this would seem to be the easiest solution. > > Option 4: > Another option would be to add a date field (DateCreated) to tblPatient > and set its default to NOW(). Then all you have to do is identify records > that were created at a certain date and time and delete them. > > DELETE > FROM tblPatient > WHERE DateCreated Between #2008-12-18 13:59:00# and #2008-12-18 14:00:00# > i tried the replace but i couldn't delete the original records due to dependancies. Show quoteHide quote > John Spencer > Access MVP 2002-2005, 2007-2008 > The Hilltop Institute > University of Maryland Baltimore County > > mcnewsxp wrote: >> "Steve" <nonse***@nomsense.com> wrote in message >> news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... >>> Try creating an unmatched query and changing it to a delete query. >>> There's a wixard for creating an unmatched query by going to queries in >>> the database window and clicking new. >>> >> >> good idea. >> except when i do this no fields show up in the list....??? >> >>> >>> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >>> news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >>>> how can i delete records from one table if the records do not exist in >>>> a matching table? i am making a backup of a table before an import is >>>> performed. the users may need to undo the backup so i need to be able >>>> to delete only the new records. this is not working, but you get the >>>> idea: >>>> >>>> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >>>> tblPatient.PatientID = tblPatientBackup.PatientID >>>> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >>>> >>>> tia, >>>> mcnewxp >>>> >>> >> If you make a backup of the original table and then import new records into
the backup table, the new records will not be in the original table. Your unmatched query should look at the backup table to find records that are not in the original table. If you do not return any records, are you specifying an appropriate field in the unmatched query? I am a bit puzzeled by your response "....no fields show up in the list...." By "fields" do you mean records or do you truly mean fields? Please clarify. Steve Show quoteHide quote "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message news:uONYxNTYJHA.1532@TK2MSFTNGP03.phx.gbl... > > "Steve" <nonse***@nomsense.com> wrote in message > news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... >> Try creating an unmatched query and changing it to a delete query. >> There's a wixard for creating an unmatched query by going to queries in >> the database window and clicking new. >> > > good idea. > except when i do this no fields show up in the list....??? > >> >> >> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >> news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >>> how can i delete records from one table if the records do not exist in a >>> matching table? i am making a backup of a table before an import is >>> performed. the users may need to undo the backup so i need to be able >>> to delete only the new records. this is not working, but you get the >>> idea: >>> >>> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >>> tblPatient.PatientID = tblPatientBackup.PatientID >>> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >>> >>> tia, >>> mcnewxp >>> >> >> > > "Steve" <nonse***@nomsense.com> wrote in message when i go to create the unmatched query after selecting the two tables - on news:x7mdnajbA5oxPNfUnZ2dnUVZ_gGdnZ2d@earthlink.com... > If you make a backup of the original table and then import new records > into the backup table, the new records will not be in the original table. > Your unmatched query should look at the backup table to find records that > are not in the original table. If you do not return any records, are you > specifying an appropriate field in the unmatched query? > > I am a bit puzzeled by your response "....no fields show up in the > list...." By "fields" do you mean records or do you truly mean fields? > Please clarify. > the form where you select the fields to match theres nothing in the lists...? Show quoteHide quote > "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message > news:uONYxNTYJHA.1532@TK2MSFTNGP03.phx.gbl... >> >> "Steve" <nonse***@nomsense.com> wrote in message >> news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... >>> Try creating an unmatched query and changing it to a delete query. >>> There's a wixard for creating an unmatched query by going to queries in >>> the database window and clicking new. >>> >> >> good idea. >> except when i do this no fields show up in the list....??? >> >>> >>> >>> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >>> news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >>>> how can i delete records from one table if the records do not exist in >>>> a matching table? i am making a backup of a table before an import is >>>> performed. the users may need to undo the backup so i need to be able >>>> to delete only the new records. this is not working, but you get the >>>> idea: >>>> >>>> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >>>> tblPatient.PatientID = tblPatientBackup.PatientID >>>> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >>>> >>>> tia, >>>> mcnewxp >>>> >>> >>> >> >> > > Are you talking about the wizard and the two listboxes for selecting fields
to match? Steve Show quoteHide quote "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message news:uzKrXYUYJHA.5156@TK2MSFTNGP04.phx.gbl... > > "Steve" <nonse***@nomsense.com> wrote in message > news:x7mdnajbA5oxPNfUnZ2dnUVZ_gGdnZ2d@earthlink.com... >> If you make a backup of the original table and then import new records >> into the backup table, the new records will not be in the original table. >> Your unmatched query should look at the backup table to find records that >> are not in the original table. If you do not return any records, are you >> specifying an appropriate field in the unmatched query? >> >> I am a bit puzzeled by your response "....no fields show up in the >> list...." By "fields" do you mean records or do you truly mean fields? >> Please clarify. >> > > when i go to create the unmatched query after selecting the two tables - > on the form where you select the fields to match theres nothing in the > lists...? > >> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >> news:uONYxNTYJHA.1532@TK2MSFTNGP03.phx.gbl... >>> >>> "Steve" <nonse***@nomsense.com> wrote in message >>> news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... >>>> Try creating an unmatched query and changing it to a delete query. >>>> There's a wixard for creating an unmatched query by going to queries in >>>> the database window and clicking new. >>>> >>> >>> good idea. >>> except when i do this no fields show up in the list....??? >>> >>>> >>>> >>>> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >>>> news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >>>>> how can i delete records from one table if the records do not exist in >>>>> a matching table? i am making a backup of a table before an import is >>>>> performed. the users may need to undo the backup so i need to be able >>>>> to delete only the new records. this is not working, but you get the >>>>> idea: >>>>> >>>>> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >>>>> tblPatient.PatientID = tblPatientBackup.PatientID >>>>> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >>>>> >>>>> tia, >>>>> mcnewxp >>>>> >>>> >>>> >>> >>> >> >> > > "Steve" <nonse***@nomsense.com> wrote in message yesnews:VZ6dnZMs14cBl9HUnZ2dnUVZ_tudnZ2d@earthlink.com... > Are you talking about the wizard and the two listboxes for selecting > fields to match? > Show quoteHide quote > > "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message > news:uzKrXYUYJHA.5156@TK2MSFTNGP04.phx.gbl... >> >> "Steve" <nonse***@nomsense.com> wrote in message >> news:x7mdnajbA5oxPNfUnZ2dnUVZ_gGdnZ2d@earthlink.com... >>> If you make a backup of the original table and then import new records >>> into the backup table, the new records will not be in the original >>> table. Your unmatched query should look at the backup table to find >>> records that are not in the original table. If you do not return any >>> records, are you specifying an appropriate field in the unmatched query? >>> >>> I am a bit puzzeled by your response "....no fields show up in the >>> list...." By "fields" do you mean records or do you truly mean fields? >>> Please clarify. >>> >> >> when i go to create the unmatched query after selecting the two tables - >> on the form where you select the fields to match theres nothing in the >> lists...? >> >>> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >>> news:uONYxNTYJHA.1532@TK2MSFTNGP03.phx.gbl... >>>> >>>> "Steve" <nonse***@nomsense.com> wrote in message >>>> news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... >>>>> Try creating an unmatched query and changing it to a delete query. >>>>> There's a wixard for creating an unmatched query by going to queries >>>>> in the database window and clicking new. >>>>> >>>> >>>> good idea. >>>> except when i do this no fields show up in the list....??? >>>> >>>>> >>>>> >>>>> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >>>>> news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >>>>>> how can i delete records from one table if the records do not exist >>>>>> in a matching table? i am making a backup of a table before an >>>>>> import is performed. the users may need to undo the backup so i need >>>>>> to be able to delete only the new records. this is not working, but >>>>>> you get the idea: >>>>>> >>>>>> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >>>>>> tblPatient.PatientID = tblPatientBackup.PatientID >>>>>> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >>>>>> >>>>>> tia, >>>>>> mcnewxp >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > 1. When you go to New - Unmatched Query to start the wizard, do you get a
listbox with all your tables? 2. You need to select the Backup table first and then the original table. Then do you get two listboxes where the left listbox shows the fields in the backup table and the right list box shows the fields in the original table? Steve Show quoteHide quote "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message news:OZ1ImC4YJHA.556@TK2MSFTNGP06.phx.gbl... > > "Steve" <nonse***@nomsense.com> wrote in message > news:VZ6dnZMs14cBl9HUnZ2dnUVZ_tudnZ2d@earthlink.com... >> Are you talking about the wizard and the two listboxes for selecting >> fields to match? >> > > yes > >> >> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >> news:uzKrXYUYJHA.5156@TK2MSFTNGP04.phx.gbl... >>> >>> "Steve" <nonse***@nomsense.com> wrote in message >>> news:x7mdnajbA5oxPNfUnZ2dnUVZ_gGdnZ2d@earthlink.com... >>>> If you make a backup of the original table and then import new records >>>> into the backup table, the new records will not be in the original >>>> table. Your unmatched query should look at the backup table to find >>>> records that are not in the original table. If you do not return any >>>> records, are you specifying an appropriate field in the unmatched >>>> query? >>>> >>>> I am a bit puzzeled by your response "....no fields show up in the >>>> list...." By "fields" do you mean records or do you truly mean fields? >>>> Please clarify. >>>> >>> >>> when i go to create the unmatched query after selecting the two tables - >>> on the form where you select the fields to match theres nothing in the >>> lists...? >>> >>>> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >>>> news:uONYxNTYJHA.1532@TK2MSFTNGP03.phx.gbl... >>>>> >>>>> "Steve" <nonse***@nomsense.com> wrote in message >>>>> news:i5WdnQ-wFJyMHNfUnZ2dnUVZ_qzinZ2d@earthlink.com... >>>>>> Try creating an unmatched query and changing it to a delete query. >>>>>> There's a wixard for creating an unmatched query by going to queries >>>>>> in the database window and clicking new. >>>>>> >>>>> >>>>> good idea. >>>>> except when i do this no fields show up in the list....??? >>>>> >>>>>> >>>>>> >>>>>> "mcnewsxp" <mcbassgui***@yahoo.com> wrote in message >>>>>> news:OuGHo2SYJHA.2280@TK2MSFTNGP06.phx.gbl... >>>>>>> how can i delete records from one table if the records do not exist >>>>>>> in a matching table? i am making a backup of a table before an >>>>>>> import is performed. the users may need to undo the backup so i >>>>>>> need to be able to delete only the new records. this is not working, >>>>>>> but you get the idea: >>>>>>> >>>>>>> DELETE tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON >>>>>>> tblPatient.PatientID = tblPatientBackup.PatientID >>>>>>> WHERE tblPatient.PatientID <> tblPatientBackup.PatientID; >>>>>>> >>>>>>> tia, >>>>>>> mcnewxp >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  
Other interesting topics
Use a current record to enter new data but keep the old data
Field value as default on subform how do i remove a "replica"? Formatting number columns in a report Permission Denied 2007 Need to create a search based on a specific field Search question Form involving multiple parents and one child Default Value How do i unhide a table in MS Access? |
|||||||||||||||||||||||
