November 17, 2015 at 1:33 pm
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
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy