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

Posted on 2006-05-31
Last Modified: 2008-03-06
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.

Question by:PSSupport
    1 Comment
    LVL 45

    Accepted Solution

    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL Duplicate Query 9 354
    same record with different status 10 235
    db2 - u 3 222
    Tricky SQL question 13 373
    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now