php - 警告:PDOStatement::execute():SQLSTATE[42000]:语法错误或访问冲突:1064

Question

我正在尝试对更新查询进行内部联接，但收到有关语法的错误，据我所知，它看起来很好(我可能正在查看某些内容)所有表格都在那里，我已将其包含在这篇文章中。

我的错误

Warning: PDOStatement::execute(): 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 'FROM applications INNER JOIN jobs ON applications.app'

我的 SQL

 $sql = "UPDATE applications 
         SET    applications.application_status = 1 
         FROM   applications
         INNER JOIN jobs 
         ON applications.application_job = jobs.job_id 
         WHERE  applications.application_user = ?
         AND jobs.job_enabled=1";

工作表我正在内部加入

CREATE TABLE IF NOT EXISTS `jobs` (
  `job_id` int(11) NOT NULL,
  `job_name` varchar(100) NOT NULL,
  `job_description` text NOT NULL,
  `job_duration` int(11) NOT NULL,
  `job_country` varchar(100) NOT NULL,
  `job_category` int(50) NOT NULL,
  `job_user` int(100) NOT NULL,
  `job_employer` varchar(100) NOT NULL,
  `job_enabled` int(2) NOT NULL DEFAULT '1',
  `job_startdate` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

我正在更新的应用程序表

CREATE TABLE IF NOT EXISTS `applications` (
  `application_id` int(11) NOT NULL,
  `application_user` varchar(100) NOT NULL,
  `application_date` datetime NOT NULL,
  `application_job` int(11) NOT NULL,
  `application_status` int(11) DEFAULT '0',
  `application_enabled` int(2) NOT NULL DEFAULT '1'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Answer 1

MySQL 不支持FROM Update 语句中的子句。与 SQL Server 不同，您的 join 语句也需要位于 SET 之前。您可以简单地这样做:

$sql = "UPDATE applications a
        INNER JOIN jobs j ON a.application_job = j.job_id                             
        SET    a.application_status = 1 
        WHERE  a.application_user = ?
          AND  j.jobs_enabled = 1"