Delete Query problems

[Guest]

Hi,
I am having problems with a delete query that has an inner join. I have two
table related to each other by a field SERIAL_NUMBER. I am trying to delete
records from one table based on criteria in another and I can't seem to get
it to work. Basically this is what I would like to do.

Delete tbl1.field1, tbl1.field2, tbl1.field3 FROM tbl1, tbl2 where tbl2.DATE
<1/1/2003 and tbl1.SERIAL_NUMBER = tbl2.SERIAL_NUMBER

Any ideas?

thanks

 

[Guest]

I can see two problems... first is that you've an incorrect Delete query.

Delete from....

You're not deleting columns you're deleting rows.

Next is a bummer... it looks like you're trying to delete the primary key
table leaving an orphaned foreign key in table 2... is this the case because
Access referential Integrity won't allow that.

To summarise (expecting this replay won't satisfy) you need to delete all
dependent foreign key records in related tables before you can delete the
primary key in the parent table....

Wheres the One to Many or 1 to drunk 8 relationship going? tab1 > tab2?

Cheers

 

[Guest]

Ugh! I think you answered my question. these tables were set up a long time
ago before my time. There are a bunch of processes that happen to populate
them. There is no foreign key. Primary for the main table is serial_number
and primary for the second is serial_number. Tab2 doesn't have it's own
unique identifier. I guess the long way around this is to make a table from
my criteria and then delete all records where s/n is equal to the homemade
table?
fyi. We have a bunch of records in an Oracle db on our unix box and they are
getting very large so I am trying to extract a few million old records from
several tables and zip them up and dump them on another server...good old
archiving I guess.

Thanks for you input. If you have any other ideas I would appreciate it.

Thanks,

quixote

 

[Guest]

An infamous 1 to 1 relationship... I've only heard of one justified reason to
actually have one, I'm guessing yours isn't number 2.

You solution sounds best, just keep a back up!

Regards.

 

[John Spencer (MVP)]

Try the following on a copy of your data and see if it works.

DELETE DISTINCTROW tbl1.field1, tbl1.field2, tbl1.field3
FROM tbl1 INNER JOIN tbl2