Delete 2 tables and WHERE over 3 tables

Question

I tried to look for the shortest way to delete 2 entries in database.

This entries are in touch which a third tables (marked as "c" in the following code) this one is a reference table and should not delete.

I tried do to this, but it failed. Did i have a simple syntax-bug or is it not possible in codeigniter or have i do this in another way?

$this->db->where(" (a.refid='".$this->session->ud('id')."' and b.refid='".$this->session->ud('id')."') and c.type='".$tempid."' and c.templateid=a.templateid and c.slug=b.slug");
  $this->db->delete('a.*,b.*');
  $this->db->from('table_a a, table_b b, table_c c');
  $delinfo=$this->db->affected_rows();

I want to delete in:
table a - the row which has "refid" and is in touch with table c by "type"
table b - the row which has "refid" and is in touch with table c by "slug"
table c - nothing to delete

hard to follow but if you turn `db_debug` on in database.php you might be able to see where the sql is getting hung up — Alex, Sep 28 '18 at 06:13
Identifier name 'a,b FROM table_a a, table_b b, table_c' is too long — Rene, Sep 28 '18 at 07:29
perhaps related? https://stackoverflow.com/questions/13133517/syntax-error-or-access-violation-1059-identifier-name-is-too-long — Alex, Sep 28 '18 at 07:40

score 1 · Accepted Answer · answered Sep 28 '18 at 08:56

what you need here is a delete combined with a join - Codeigniters QueryBuilder doesn't support delete statements with joins - so you have to write your own instead

something like the following should work

$this->db->query('
    DELETE a,b,c FROM table_a a
    JOIN table_c c ON (a.templatedid = c.templateid)
    JOIN table_b b ON (c.slug = b.slug)
    WHERE a.refid = ? AND c.type = ? AND b.refid = ?', 
    array($this->session->ud('id'), $tempid, $this->session->ud('id'))
);

Delete 2 tables and WHERE over 3 tables

1 Answers1