Trying to use SQL Script to clear specific data from a table.

I have a need to clear data from one table. However in order to clear the data, I need to run a query against another table to determine what needs to be deleted.

I use the following to get the data I want to delete:

select * from crpdta.f06502, crpdta.f060116 where yean8=yaan8 and yahmco='01000';

crpdta is the container (this is actually on an AS400)
f06502 is the table I want to delete Data out of
f060116 is the table I cross reference with to find the data I need to delete

I've tried various delete statements, but have been unsucessful so far.

**** UPDATE ****
Working with a couple of people in MySQL, we've gotten close...

Using:
DELETE FROM crpdta.f06502 WHERE EXISTS(select * from crpdta.f06502, crpdta.f060116 where yean8=yaan8 and yahmco='01000');

It clears the whole file however. But it got further than i have been previously.

Another option, would be to update a column with a "tagged" number, so that i can then run a delete based on this new #...

Hoping someone can assist.

Alan
LVL 1
PSSupportAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi PSSupport,

A couple of questions:

Do both tables look alike?
Do they have any common unique keys?
Do they have the same primary key?

Choosing the most efficient first, if they have the same primary key, then this should work:

DELETE FROM table1 WHERE primary_key IN (SELECT primary_key FROM table2 WHERE yean8 = yaan8 and yanmco = '01000');

If they have a common unique key, a similar query will work:

DELETE FROM table1 WHERE unique_key IN (SELECT unique_key FROM table2 WHERE yean8 = yaan8 and yanmco = '01000');


Good Luck!
Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.