使用INNER JOIN从两个表中删除数据

Question

I want to delete data from two tables with one SQL query according to datetime_lastactive and if the IP addresses is matching your own. But I'm getting this error message when I try out the following SQL query:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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 'INNER JOIN visitors_main WHERE information_ipaddress = '123.123.123.123' A' at line 2' in ...

DELETE FROM visitors_list
INNER JOIN visitors_main
WHERE information_ipaddress = :ipaddress
AND datetime_lastactive < NOW() - INTERVAL 3 HOUR

The tables looks like this:

CREATE TABLE IF NOT EXISTS `visitors_list` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_visitor` int(10) DEFAULT '0',
  `id_user` int(10) DEFAULT '0',
  `data_filename` text NOT NULL,
  `data_filename_get` text NOT NULL,
  `data_useragent` text NOT NULL,
  `datetime_lastactive` datetime NOT NULL,
  `information_ipaddress` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

CREATE TABLE IF NOT EXISTS `visitors_main` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_user` int(10) DEFAULT '0',
  `data_coordinates` varchar(25) NOT NULL,
  `datetime_firstvisit` datetime NOT NULL,
  `checkbox_anonymous` tinyint(4) DEFAULT '0',
  `checkbox_tiecoordinates` tinyint(4) DEFAULT '0',
  `checkbox_nogps` tinyint(4) DEFAULT '0',
  `information_ipaddress` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

How can I make this work?

Answer 1

Try this instead:

DELETE l, m
FROM visitors_list AS l
INNER JOIN visitors_main AS m ON l.information_ipaddress = m.information_ipaddress
WHERE l.information_ipaddress = :ipaddress
AND l.datetime_lastactive < NOW() - INTERVAL 3 HOUR;