Post reply

Need to DELETE records from JOIN statement from both tables

  • November 17, 2015 at 1:33 pm

    #399342

    Hello,

    I can't figure out how to delete records from both tables that are joined. The results I need to delete are based on the WHERE clause that returns the data. My delete statement is only deleting from the ae_job table and I need the records to also delete from the ae_jobel table.

    SELECT distinct appid, ae_jobel.docid, ae_job.jid, ae_job.jstat

    from ae_job join ae_jobel

    ON (ae_job.jid = ae_jobel.jid)

    where appid = '180'

    DELETE ap

    from ae_job as ap

    INNER JOIN ae_jobel as el

    ON ap.jid = el.jid

    WHERE LOWER(el.appid) LIKE '%180%'

  • November 17, 2015 at 2:09 pm

    #1840483

    You can only delete from one table at a time. You can use a temp table to store the ids that you want to delete. You may also be able to use the OUTPUT clause of the first DELETE as input to the second DELETE, but I've never tried it.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply