Erase data

  • Thread starter Thread starter Butter
  • Start date Start date
B

Butter

I am inporting a txt-file into a temp table. ([MyTempTable])
I there modify it a little before I finally send it over to the "real"
table. ([MyRealTable])

Now, to be sure that the records in the file has not been imported before, I
want to delete the records in [MyRealTable] if they exist in [MyTempTable]
before actually moving the records to [MyRealTable].

The field that I would like to check this with is [txtInvoiceNo].
Since it is a "Line-table" (Lines connected to the Invoice table) there will
be several records containing the same value in [txtInvoiceNo].

So, for each line in [MyTempTable]![txtInvoiceNo] I would like to delete all
records in [MyRealTable] that has the same value in the field
[txtInvoiceTable].

Feel that I am not good enought to write a loop of my own with the above
criteria, so I would really appreciate some assistance here. ;)

Thanks in advance.

/K
 
Ofc, I wrote wrong. (I blame my stressed-up wife, haha)

Should be

So, for each line in [MyTempTable] I would like to delete all
records in [MyRealTable] that has the same value in the field
[txtInvoiceNo] in both tables.

Cheers!!!
 
I am inporting a txt-file into a temp table. ([MyTempTable])
I there modify it a little before I finally send it over to the "real"
table. ([MyRealTable])

Now, to be sure that the records in the file has not been imported before, I
want to delete the records in [MyRealTable] if they exist in [MyTempTable]
before actually moving the records to [MyRealTable].
Click to expand...

Why delete them, rather than running an update query to correct them?
The field that I would like to check this with is [txtInvoiceNo].
Since it is a "Line-table" (Lines connected to the Invoice table) there will
be several records containing the same value in [txtInvoiceNo].

So, for each line in [MyTempTable]![txtInvoiceNo] I would like to delete all
records in [MyRealTable] that has the same value in the field
[txtInvoiceTable].

Feel that I am not good enought to write a loop of my own with the above
criteria, so I would really appreciate some assistance here. ;)
Click to expand...

No looping needed; just run a delete query. BACK UP YOUR DATABASE first,
deletion is irreversible! Then run a query like

DELETE [MyRealTable].*
FROM [MyRealTable] INNER JOIN [MyTempTable]
ON [MyRealTable].[txtInvoiceNo] = [MyTempTable].[txtInvoiceNo];

If that doesn't work (and it might not, depending on indexing) try

DELETE * FROM [MyRealTable]
WHERE [MyRealTable].[txtInvoiceNo] IN
(SELECT txtInvoiceNo FROM MyTempTable);
 
Hi,

I would use a delete query - the sql below should do the job, if I
understood you correctly:

DELETE FROM [MyRealTable]
WHERE [txtInvoiceNo] IN
(SELECT [txtInvoiceNo] FROM [MyTempTable])

Regards,
anlu

Butter said:
Ofc, I wrote wrong. (I blame my stressed-up wife, haha)

Should be

So, for each line in [MyTempTable] I would like to delete all
records in [MyRealTable] that has the same value in the field
[txtInvoiceNo] in both tables.

Cheers!!!


Butter said:
I am inporting a txt-file into a temp table. ([MyTempTable])
I there modify it a little before I finally send it over to the "real"
table. ([MyRealTable])

Now, to be sure that the records in the file has not been imported before, I
want to delete the records in [MyRealTable] if they exist in [MyTempTable]
before actually moving the records to [MyRealTable].

The field that I would like to check this with is [txtInvoiceNo].
Since it is a "Line-table" (Lines connected to the Invoice table) there will
be several records containing the same value in [txtInvoiceNo].

So, for each line in [MyTempTable]![txtInvoiceNo] I would like to delete all
records in [MyRealTable] that has the same value in the field
[txtInvoiceTable].

Feel that I am not good enought to write a loop of my own with the above
criteria, so I would really appreciate some assistance here. ;)

Thanks in advance.

/K
Click to expand...
Click to expand...
 
Back
Top