在内部连接表中执行删除

Question

I want to delete an entry in my PAGES table. Upon deletion of it, it will cascade to OBJECTS table. No worries in here, it's working if I delete the entry by using simple DELETE. However, I need to specify some conditions:

PAGES table

+--------------------------+--------------+------+-----+---------+----------------+
| Field                    | Type         | Null | Key | Default | Extra          |
+--------------------------+--------------+------+-----+---------+----------------+
| page_id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| users_id                 | int(11)      | NO   | MUL | NULL    |                |
| page_value               | varchar(20)  | NO   | UNI | NULL    |                |
+--------------------------+--------------+------+-----+---------+----------------+

OBJECTS table

+----------------------------+-------------+------+-----+---------+----------------+
| Field                      | Type        | Null | Key | Default | Extra          |
+----------------------------+-------------+------+-----+---------+----------------+
| objects_id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
| page_id                    | int(11)     | NO   | MUL | NULL    |                |
| objects_name               | varchar(50) | NO   |     | NULL    |                |
| objects_avail              | varchar(20) | NO   |     | NULL    |                |
+----------------------------+-------------+------+-----+---------+----------------+

If objects_avail == "ALL", I must not include that entry in cascade delete. I came up with this SQL query but got an error:

$query = "
    DELETE FROM pages AS p 
    INNER JOIN objects AS o ON p.page_id = o.page_id 
    WHERE p.page_id = ? 
      AND p.users_id = ? 
      AND p.page_value = ? 
      AND o.objects_avail != ?";

The error thrown:

["42000",1064,"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS p INNER JOIN objects AS o ON p.page_id = o.page_id WHER' at line 1"]

Example value for the PDO placeholders:

$params = array(81,5,"main page","ALL");

where all of this is valid and I'm sure this is not where the problem is.

I doubt or prettry sure I'm missing some in my query, any suggestions please?

Answer 1

For an inner join UPDATE or DELETE, you need to specify which of the tables you actually want to delete explicitly, or else the parser won't know what you mean. You can choose 1 or more tables to delete from. In your case, it makes sense to just delete p, the alias for pages.

DELETE p
FROM pages AS p
INNER JOIN objects AS o ON p.page_id = o.page_id
WHERE
    p.page_id = ? AND
    p.users_id = ? AND
    p.page_value = ? AND
    o.objects_avail != ?

The only line I changed was DELETE became DELETE p