Search code examples
mysqlsql-deletenotin

SQL 1064 - You have an error in your SQL syntax when deleting across multiple tables

I'm trying to delete specific rows in multiple log tables, for activity that is > 5 days old. but want to keep the log activity if the visitor_id is found in log_customer.

the following returns over 42 K rows: 

SELECT *
FROM `log_url` , `log_url_info`, `log_visitor_info`, `log_visitor`
WHERE `log_url`.`visit_time` <= NOW( ) - INTERVAL 5
DAY
AND `log_url`.`url_id` = `log_url_info`.`url_id` 
AND `log_url`.`visitor_id` = `log_visitor`.`visitor_id`
AND `log_url`.`visitor_id` = `log_visitor_info`.`visitor_id`
AND `log_url`.`visitor_id` NOT IN ( select `log_customer`.`visitor_id` from `log_customer` )

However, this returns the sql 1064 error: 

DELETE
FROM `log_url` , `log_url_info`, `log_visitor_info`, `log_visitor`
WHERE `log_url`.`visit_time` <= NOW( ) - INTERVAL 5
DAY
AND `log_url`.`url_id` = `log_url_info`.`url_id` 
AND `log_url`.`visitor_id` = `log_visitor`.`visitor_id`
AND `log_url`.`visitor_id` = `log_visitor_info`.`visitor_id`
AND `log_url`.`visitor_id` NOT IN ( select `log_customer`.`visitor_id` from `log_customer` )

Error messages:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'WHERE log_url.visit_time 
<= NOW( ) - INTERVAL 5 DAY AND log_url.url_id' at line 3

I also get ...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to 
your MySQL server version for the right syntax to use near 'WHERE log_url.visit_time
<= STR_TO_DATE('2014-08-21 10:00:00', '%Y-%m-%d %H:' at line 3

...if I replace NOW() ... with <= STR_TO_DATE( '2014-08-21 10:00:00', '%Y-%m-%d %H:%i:%s' )

Solution

  • Shouldn't that be...

    DELETE `log_url` , `log_url_info`, `log_visitor_info`, `log_visitor`
FROM `log_url` , `log_url_info`, `log_visitor_info`, `log_visitor`
WHERE `log_url`.`visit_time` <= NOW( ) - INTERVAL 5 DAY
AND `log_url`.`url_id` = `log_url_info`.`url_id` 
AND `log_url`.`visitor_id` = `log_visitor`.`visitor_id`
AND `log_url`.`visitor_id` = `log_visitor_info`.`visitor_id`
AND `log_url`.`visitor_id` NOT IN ( select `log_customer`.`visitor_id` from log_customer` )

    or even...

    DELETE u 
     , ui
     , vi
     , v
  FROM log_url u
  JOIN log_url_info ui
    ON ui.url_id = u.url_id 
  JOIN log_visitor_info vi
    ON vi.visitor_id = u.visitor_id 
  JOIN log_visitor v
    ON v.visitor_id = u.visitor_id 
 WHERE u.visit_time <= NOW( ) - INTERVAL 5 DAY
   AND u.visitor_id NOT IN ( SELECT visitor_id FROM log_customer)