Search code examples
sqloraclesql-delete

sqlplus DELETE statement

I'm using sqlplus (oracle). I trying to write a statement to remove all authors that have not yet published a book based on the bookauthor table. Since I have joined to tables , I don't know how to delete authors that have not yet published a book. Also I'm not sure If I got subquery correctly in order to get an output of authors who have not published a book. 

DELETE 
FROM bookauthor, books
WHERE authorid = 
(SELECT authorid, count(*) pubdate
FROM   bookauthor 
JOIN books USING (isbn)
GROUP BY authorid
HAVING count(*) < 1);

This is what I have so far. I can't get it run correctly.

For table books -> isbn, title, pubdate, pubid, cost, retail, discount, category
for table bookauthor -> isbn, authorid

Solution 
  • DELETE 
FROM bookauthor
WHERE authorid =
                (SELECT authorid FROM bookauthor WHERE isbn NOT IN (SELECT isbn FROM books))

    Try this. 

    DELETE 
FROM bookauthor
WHERE authorid =
                (SELECT authorid FROM bookauthor a LEFT OUTER JOIN books b ON a.isbn = b.isbn WHERE b.isbn IS null)

    Or this.