2

I am writing a function to delete all the rows matching a certain query.

If I select the matching row ids, and then delete the rows with the id, in two steps, then it works. Thus I know that my query to find the rows to delete is correct:

    sql = """SELECT MSN.id
                 FROM schedule_missingschedulenames as MSN,
                      names_parsedname as PN,
                      names_simplifiednamemapping as SNM,
                      names_canonicalname as CN
                WHERE MSN.parsed_name_id = PN.id
                  AND PN.simplified_name = SNM.simplified_name
                  AND SNM.canonical_name_id = CN.id
                  AND CN.category_id = %s""" % cat.id

    ids = [row.id for row in MissingScheduleNames.objects.raw(sql)]
    MissingScheduleNames.objects.filter(id__in=ids).delete()

However, if I attempt to do DELETE in one query as it should, it does not work:

    sql = """DELETE FROM schedule_missingschedulenames WHERE id in (SELECT MSN.id
                 FROM schedule_missingschedulenames as MSN,
                      names_parsedname as PN,
                      names_simplifiednamemapping as SNM,
                      names_canonicalname as CN
                WHERE MSN.parsed_name_id = PN.id
                  AND PN.simplified_name = SNM.simplified_name
                  AND SNM.canonical_name_id = CN.id
                  AND CN.category_id = %s)""" % cat.id
    MissingScheduleNames.objects.raw(sql)

This second query simply doesn't delete anything.

Where am I going wrong with the DELETE query?

Claudiu
  • 224,032
  • 165
  • 485
  • 680
  • 3
    If you ran the first query, then perhaps all the appropriate rows are already deleted. You should also learn to use proper `JOIN` syntax. – Gordon Linoff Jun 24 '16 at 18:14
  • better move those joins to `WITH` part and delete with `CTE`. https://www.postgresql.org/docs/current/static/queries-with.html – Vao Tsun Jun 24 '16 at 19:11
  • @GordonLinoff: Ah they each ran under different test runs of course :). About the proper `JOIN` syntax, what's the benefit? I'm asking from a state of ignorance. I thought performance-wise it's the same? http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Claudiu Jun 24 '16 at 19:11

0 Answers0