CODEWITHSUNDEEP
mysql
cory baxter
cory baxter

Reputation: 53

What am I doing wrong with the following delete query?

I want to create a procedure in MYSQL that will run every hour that will delete all entries in the Document table. I want to delete all Documents that have access level of 5 if they are posted by people from France after one year if these users were under 18 years of age at the time the Document was posted

The code below gives me an error and tells me check my syntax. Does anyone have any ideas on how to fix the code up so its a working procedure?

CREATE EVENT hourlydelete
ON SCHEDULE EVERY 1 HOUR
DO CALL delete_Document();

DELIMITER $$ 
CREATE PROCEDURE delete_Document()
BEGIN
    DELETE FROM Document
    WHERE   (Document.idPerson = Person.idPerson AND Person.Country = 'France' AND Document.AccessLevel = 5)
    AND     ((DATEDIFF(Document.Posted,Person.DateOfBirth) / 365) < 18) 
    AND     ((DATEDIFF(NOW(), Document.Posted)/365) > 3)
    ORDER by Document.idPerson

    END
    $$
    DELIMITER;

Upvotes: 2

Views: 66

Answers (2)

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

Remove

ORDER by Document.idPerson

Since, you're not using a SELECT query there is no resultset to ORDER BY.

The multi-table syntax for DELETE statement in MySQL is

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id = t2.id AND t2.id = t3.id;

where the records get deleted from t1 and t2 (before USING) but not t3.

So, you actually need to use

DELETE FROM Document USING Document INNER JOIN Person
WHERE ...

This makes sure that only Document records get deleted and Person is used to specify the criterion only. Also, note that ORDER BY and LIMIT cannot be used with multi-table DELETEs.

Upvotes: 3

Bohemian
Bohemian

Reputation: 424993

You are missing a semi-colon ; at the end of the DELETE statement!

Also, your conditions seem to be "either a) or b) or c)", but you've used AND logic, when you probably want OR logic.

Change the main AND operators to OR, and add the semi-colon:

DELETE FROM Document
WHERE (Document.idPerson = Person.idPerson AND Person.Country = 'France' AND Document.AccessLevel = 5)
OR ((DATEDIFF(Document.Posted,Person.DateOfBirth) / 365) < 18) 
OR ((DATEDIFF(NOW(), Document.Posted)/365) > 3); -- Add a semi colon!

Upvotes: 2

Related Questions