duanhuo3392
duanhuo3392
2013-12-22 09:54

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

已采纳

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • donglu4633 donglu4633 8年前

    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;
    
    点赞 评论 复制链接分享
  • douxiluan6555 douxiluan6555 8年前
    DELETE v1,v2
    FROM visitors_list v1
    INNER JOIN visitors_main v2 ON v1.id_visitor = v2.id
    WHERE v1.information_ipaddress = :ipaddress
    AND v1.datetime_lastactive < NOW() - INTERVAL 3 HOUR;
    
    点赞 评论 复制链接分享

相关推荐