How to do Deletes

[cfis]

Creating a separate ticket for this issue. Should create on for updates also. There are 6 databases we test against - Postgres, MariaDb, Sqlite, MySQL, Oracle and SQLServer.

IN

Currently CPK uses an IN clause to do deletions - this is what stock Rails does also:

DELETE
FROM reference_codes 
WHERE (reference_codes.reference_type_id, reference_codes.reference_code) IN
     (SELECT reference_codes.reference_type_id, reference_codes.reference_code FROM reference_codes)

Recently supported - SQLite (you need a newer version, I'm not sure which one but this now works and once upon a time it did not). Not supported - MySQL, SQLServer

EXISTS

DELETE parent
FROM `reference_codes` parent
WHERE EXISTS (SELECT 1
              FROM `reference_codes`
              WHERE `parent`.`reference_type_id` = `reference_type_id` AND
                    `parent`.`reference_code` = `reference_code`)

Not supported - MySQL

JOIN

Unfortunately, there isn't a standard way to do this and it would require updating Arel. But:

MySQL and MariaDB

DELETE reference_codes 
FROM reference_codes 
INNER JOIN (SELECT reference_codes.reference_type_id, reference_codes.reference_code 
                     FROM reference_codes) cpk_delete 
     ON reference_codes.reference_type_id = cpk_delete.reference_type_id AND
           reference_codes.reference_code cpk_delete.reference_code

Postgresql:

DELETE
FROM reference_codes 
USING (SELECT reference_codes.reference_type_id, reference_codes.reference_code FROM reference_codes) cpk_delete
WHERE reference_codes.reference_type_id = cpk_delete.reference_type_id AND reference_codes.reference_code = cpk_delete.reference_code

SQLServer:

DELETE [reference_codes]
FROM [reference_codes]
JOIN (SELECT [reference_codes].[reference_type_id], [reference_codes].[reference_code] 
      FROM [reference_codes]) AS cpk_delete ON 
      [reference_codes].[reference_type_id] = [cpk_delete].[reference_type_id] AND
      [reference_codes].[reference_code] = [cpk_delete].[reference_code]

Not supported - SQLite

Conclusion

The most generic approach is using IN but we have to special case MySQL and SQLServer to use joins (or the older CPK approach with a whole bunch of nested subqueries but I believe that's not good from a performance standpoint).

[cfis]

Note that MySQL will actually do IN queries - the error is that you can reference a table you are updating. So previously we obfuscated that by doing this:

DELETE FROM 'reference_codes'
WHERE (reference_codes.reference_type_id, reference_codes.reference_code) IN 
  (SELECT reference_type_id,reference_code
   FROM (SELECT DISTINCT 'reference_codes'.'reference_type_id', 'reference_codes'.'reference_code' 
               FROM 'reference_codes') __active_record_temp)

Notice the double nesting to hide from MySql that the subquery is using reference_codes.