I am try to this things,
When i delete state also delete this state city delete.
my query
DELETE FROM ab
FROM City AS ab INNER JOIN
State AS b ON ab.state_id = b.state_id CROSS JOIN
ab
WHERE (ab.state_id =
(SELECT state_id
FROM State
WHERE (state_name = @state_name)))
city_id,city_name
| 11 | Rajasthan |
| 12 | Gujarat |
| 13 | Maharastra |
My city table
city_id,city_name,state_id
| 12 | Ahmedabad | 12 |
| 14 | Borivali | 13 |
| 16 | Surat | 12 |
| 19 | Udaypur | 11 |
| 20 | Mumbai | 13 |
| 21 | Pune | 10 |
| 22 | Vadodara | 12 |
| 25 | Anand | 12 |
Error Message : Invalid object name 'ab'
Guest UserPosted Apr 6, 2011, 10:28 AM
Try this subquery instead:
DELETE FROM city
WHERE state_id IN (SELECT state_id FROM State WHERE state_name = @state_name)