[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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...

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.

1 Solution
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!

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now