CODEWITHSUNDEEP
mysql
Pottsiex5
Pottsiex5

Reputation: 497

Delete row from table conditional on a related table

I have two tables: Store and Company. I want to be able to delete a store based on the information of its company. Initially I tried:

DELETE FROM Store WHERE (SELECT * FROM Company WHERE companyValueA > 5).company_id = store.company_id AND store.wf = 0;

But this isnt working. I was thinking about doing a join of the two and deleting from the join like this:

DELETE FROM (SELECT * FROM Store LEFT JOIN Company ON Store.company_id = Company.company_id) WHERE companyValueA > 5 AND wf = 0;

but that isnt working either. Does anyone know how I can accomplish this?

Upvotes: 0

Views: 88

Answers (2)

archmicrobe
archmicrobe

Reputation: 1

You could use the MYSQL's DELETE with JOIN syntax (i haven't used it) or just collect all the store id's to be deleted with subquery if you need a more compatible statement:

DELETE
  FROM store
 WHERE store_id IN (
                     SELECT S.store_id
                       FROM store S, company C
                      WHERE C.company_id = S.company_id
                        AND C.companyValueA > 5
                        AND S.wf = 0
                     )

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Just a guess... obviously, make a backup before attempting this!!!

DELETE s 
  FROM store s
  JOIN company c
    ON c.company_id = s.company_id 
 WHERE c.companyValueA > 5
   AND s.wf = 0;

Upvotes: 1

Related Questions