Search code examples
mysqlsqlsql-delete

Sql query only functions if all records exist

DELETE FROM Profile, Images, Schedules 
 USING Profile 
INNER JOIN Images USING(profile_id) 
INNER JOIN Schedules USING(profile_id) 
     WHERE Profile.profile_id = 47

I have this piece of mysql code that deletes all the records that has profile 47. However let's say Schedules doesn't have 47, then the whole query doesnt delete the other records from the other tables.

Basically I want it to delete everything regardless of whether or not schedules has a record.

The other option is query the database to check the Schedules table before doing the delete query?

Solution

  • Use an OUTER join to access the tables that might not have supporting records:

    DELETE FROM p, i, s
      USING PROFILE p 
  LEFT JOIN IMAGES i ON i.profile_id = p.profile_id
  LEFT JOIN SCHEDULES s ON s.profile_id = p.profile_id
      WHERE p.profile_id = 47

    Here's a good primer on JOINs.