CODEWITHSUNDEEP
sqloracle-database
nonickname
nonickname

Reputation: 301

How to delete records in one table based on the values in another table

I need to delete the same row in table1 based on the value in table2.

I tried this:

DELETE FROM project.wish_list
  USING project.wish_list
  INNER JOIN project.book ON project.wish_list.quantity = project.book.quantity
  WHERE (project.wish_list.quantity = 0);

If quantity = 0 in table book, the query should delete the row (with equal isbn) in wish_list.

wish_list and book are similar tables.

Is it possible?

Or an UPDATE of the quantity to -1 for all isbn in the wish_list where the quantity in the book table is 0?

EDIT: delete duplicated with ISBN equal

ISBN        title
-------------------
123        Apples      <----- delete
123        Apples      <----- delete
123        Apples      <----- One only has to remain
1234       Pineapple   <-----  NO DELETE

Upvotes: 0

Views: 3581

Answers (2)

JP Alpano
JP Alpano

Reputation: 120

Sorry, i replied to the wrong question.

Upvotes: 0

Glenn
Glenn

Reputation: 9150

Not quite clear about the question. Sounds like: remove all wish_list entries for books that have quantity 0 in the book table?

DELETE FROM wish_list
  WHERE isbn IN ( SELECT isbn
                    FROM book
                    WHERE quantity = 0 );

Or the update:

UPDATE wish_list
  SET quantity = -1
  WHERE isbn IN ( SELECT isbn
                    FROM book
                    WHERE quantity = 0 );

Update: Deleting duplicates from wish_list (regardless of quantity). Please test, I haven't:

DELETE FROM wish_list
  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM wish_list GROUP BY isbn);

Upvotes: 3

Related Questions