Link to home
Create AccountLog in
Avatar of anastasiasoul
anastasiasoulFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL DELETE

Hi, I need to delete * from wnight for admission.outdate more than one month old.
I have got the qry, but when i try to execute it there is an error message:

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

Cannot figure out where the problem is.
DELETE 
FROM wnight
WHERE wnight.adno IN
(SELECT wnight.adno
FROM 
wnight, 
admission, 
currentdate
WHERE
admission.adno=wnight.adno
AND admission.outdate< SUBDATE(currentdate , INTERVAL 1 MONTH));

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of virmaior
virmaior
Flag of United States of America image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of kojoru
kojoru
Flag of Russian Federation image
It seems that you've got the same problem as http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

You cannot update a table and select from the same table in a subquery in MySql.

I can't figure out the easy reformulation of your query. There, however is a hack that will probably make your code work. 

DELETE 
FROM wnight
WHERE wnight.adno IN
(SELECT wnight.adno
FROM 
(SELECT wnight.adno FROM wnight), 
admission, 
currentdate
WHERE
admission.adno=wnight.adno
AND admission.outdate< SUBDATE(currentdate , INTERVAL 1 MONTH));

Open in new window

Avatar of anastasiasoul
anastasiasoul
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thank you for a quick and accurate solution