CODEWITHSUNDEEP
phpmysql
Aishy
Aishy

Reputation: 45

Delete a row from a table which contains the primary key

I have two MySQL tables called distance and place as follows:

distance

╔════╦══════╦═══════╗
║ id ║ name ║ point ║
╠════╬══════╬═══════╣
║ 1  ║ Call ║   A   ║
║ 2  ║ Foo  ║   B   ║
╚════╩══════╩═══════╝

place

╔═══════╦═════════╗
║ point ║ address ║
╠═══════╬═════════╣
║   A   ║  ABCD   ║
║   B   ║  CDEF   ║
╚═══════╩═════════╝

'id' is the primary key of the 'distance' table and 'point' is the primary key of the 'place' table. 'point' is the foreign key of the 'distance' table which is referred by the 'place' table.
I want to delete the row of point 'A' from 'place' table which is the id is equals to '1'. As this foreign key is created with 'on delete cascade constraint', the relevant row of the place table should also be deleted. (That means, according to the following tables, the first row of the distance table and the first row of the place table should be deleted).

This is the query I have tried so far under PHP code:

mysql_query("delete p.point from place p where p.point IN SELECT * FROM (SELECT d.point FROM distance d GROUP BY d.point where d.id='1' )");

But, this does not delete the rows. No error messages. Can anyone please help me to solve this problem?

Upvotes: 0

Views: 97

Answers (5)

Yuuu
Yuuu

Reputation: 174

Try to do this

DELETE FROM place p
WHERE p.point IN (SELECT d.point 
                  FROM distance d 
                 WHERE d.id=1)

So the PHP Query should be like this

mysql_query('DELETE FROM place p WHERE p.point IN (SELECT d.point FROM distance d WHERE d.id=1)')

The Sub-Sub Select you have done probably is returning no value, so no action is made and no error is given because the query is done correctly.

Upvotes: 0

Divakarcool
Divakarcool

Reputation: 481

Try This :- 

DELETE p.* FROM place p
INNER JOIN distance d ON p.point = d.point
WHERE d.id = 1;

Upvotes: 0

Gvidas
Gvidas

Reputation: 1964

You use * in your WHERE IN statement. You need to select only ids if you want to use WHERE IN. Here's your fixed code:

mysql_query("delete p from place p where p.point IN (SELECT p.point FROM (SELECT d.point FROM distance d GROUP BY d.point where d.id='1' ))");

But i don't understand why to use two select when you can do just like this 

mysql_query("delete p from place p where p.point IN (SELECT d.point FROM distance d GROUP BY d.point where d.id='1' )");

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Try using an INNER JOIN in your DELETE query:

DELETE p FROM place p
INNER JOIN distance d ON p.point = d.point
WHERE (d.id = 1);

Upvotes: 2

Zafar Malik
Zafar Malik

Reputation: 6844

Solution1: You are missing "(" after in clause in your query.

Solution2: Better option is join-

delete pl.* from place pl 
join distance dst on dst.point=pl.point 
where d.id=1;

Solution3: Best option will be just delete from parent table and all related rows will be deleted automatically from child as you are using on delete cascade functionality.

DELETE FROM place WHERE `point`='A';

Upvotes: 0

Related Questions