I am trying to delete a project f开发者_Go百科rom the projects table and all the images associated with that project in the images table.

Lets say p_id = 10

DELETE FROM projects, images WHERE projects.p_id = ? AND images.p_id = ?

What is wrong with this query?

DELETE projects, images 
FROM projects, images 
WHERE projects.p_id = ? 
AND projects.p_id = images.p_id;

As Chacha102 noted, the problem of your query was the AND in the WHERE clause.

However, you may want to use the JOIN syntax for multi-table DELETEs, which I find easier to read:

DELETE     projects, images
FROM       projects 
LEFT JOIN  images ON images.p_id = projects.p_id
WHERE      projects.p_id = 10;

DELETE FROM projects, images WHERE projects.p_id = ? or images.p_id = ?

When being deleted, an item will never meet both of these requirements, therefore it must be OR not AND

The answer

DELETE FROM p, i
USING projects p, images i
WHERE p.p_id = ?
  AND p.p_id = i.p_id

The test

projects

create table projects (
  p_id int unsigned not null auto_increment primary key
);
insert into projects (p_id) values (1),(2),(3);
select * from projects;
-- +------+
-- | p_id |
-- +------+
-- |    1 | 
-- |    2 | 
-- |    3 | 
-- +------+

images

create table images (
  i_id int unsigned not null auto_increment primary key,
  p_id int unsigned default null
);
insert into images (p_id) values (1),(1),(1),(2),(2),(3),(3);
select * from images;
-- +------+------+
-- | i_id | p_id |
-- +------+------+
-- |    1 |    1 | 
-- |    2 |    1 | 
-- |    3 |    1 | 
-- |    4 |    2 | 
-- |    5 |    2 | 
-- |    6 |    3 | 
-- |    7 |    3 | 
-- +------+------+

the delete

delete from p, i
using projects p, images i
where p.p_id = i.p_id
 and  p.p_id = 1;

the result

select * from projects;
-- +------+
-- | p_id |
-- +------+
-- |    2 | 
-- |    3 | 
-- +------+

select * from images;
-- +------+------+
-- | i_id | p_id |
-- +------+------+
-- |    4 |    2 | 
-- |    5 |    2 | 
-- |    6 |    3 | 
-- |    7 |    3 | 
-- +------+------+

works a treat!

You should use two separate queries to do that :

delete from images where p_id = 123;

delete from projects where p_id = 123;

i.e. :

• First, delete the images, that depend on the project (foreign key ? )
• And, when nothing depends on the project anymore, delete the project itself.

And, as a security precaution, you should wrap all this in a transaction, to get a all or nothing behavior -- well, if you are using a storage engine that suppors transactions, like InnoDb.

See 12.3.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax, about that, in the MySQL Manual.

Change the AND into an OR.

You might want to use a foreign key constraint with a cascading delete, much easier, but you have to use innoDB and create this FK-constraint. Delete the project and all related images will be deleted as well.

(Wrong answer, MySQL allows this)

You can't delete from two tables in one query.

The closest you can get is wrap the two deletes in a transaction:

begin transaction
delete from projects where p_id = ?
delete from images where p_id = ?
commit transaction