[SOLVED] Asql select join and delete together

Issue

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

Solution

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

Answered By – Abhik Chakraborty

Answer Checked By – Katrina (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.