SQL Delete Rows Based on Another Table

Delete all rows in a table based on another table

DELETE Table1
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID

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

delete 
from table1
where cm_id IN (select um_id from table2 where order_num between 518 and 520)

SQL Delete Rows Based on Another Table

I think this is what you want:

DELETE FROM `table1`
WHERE `group` in (SELECT DISTINCT `group` FROM `table2`)

Delete rows depending on values from another table

You were very close. I suggest you use NOT IN instead of IN:

DELETE FROM Customer 
  WHERE CustID = $1 AND
        CustID NOT IN (SELECT DISTINCT CustID
                         FROM Order
                         WHERE Status = 'A');

I'm guessing here that Status = 'A' means "active order". If it's something else change the code appropriately.

How do I delete rows in one table where the ID matches another table row where a field is a certain value?

try apply join permissions and user and where status = 'T'

Example:

DELETE p
FROM permissions p
INNER JOIN users u
  ON p.user_id=u.id
WHERE u.status = 'T'

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

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);

---

Related Topics