CODEWITHSUNDEEP
mysqlsql
Alberto Alibaba
Alberto Alibaba

Reputation: 305

Asql select join and delete together

table master

|  id  |   user  |
|  1   |  user1  |
|  3   |  user2  |
|  4   |  user3  |

table ver

| id_v |  value  |
|  2   |  value1 |
|  3   |  value2 |

I must delete id_v 2 on table ver

I' have this query

DELETE FROM `ver` WHERE id_v in (SELECT v.id_v
FROM master m
RIGHT JOIN ver v
ON m.id = v.id_v
WHERE m.id IS NULL)

but this is the error

You can't specify target table 'ver' for update in FROM clause

Upvotes: 0

Views: 94

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

It could be done using the following

delete 
v
from ver v
left join master m on m.id = v.id_v
where m.id is null

Demo

Upvotes: 1

Yang
Yang

Reputation: 202

You can't use a subquery of table ver to delete something from table ver itself.

You mean this?

delete from ver where id_v is null;

Upvotes: 0

Anik Islam Abhi
Anik Islam Abhi

Reputation: 25352

Try this 

DELETE FROM `ver` WHERE id_v in ( SELECT id_v from
(SELECT v.id_v FROM master m
RIGHT JOIN ver v
ON m.id = v.id_v
WHERE m.id IS NULL) as temp )

Upvotes: 1

Related Questions