警告：PDOStatement :: execute（）：SQLSTATE [42000]：语法错误或访问冲突：1064

问题描述：

I am trying to inner join on an update query but am getting an error about my syntax, to my knowledge it looks fine (I maybe looking over something) all the tables are there which I have included in this post for you.

My error

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'

My 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";

Jobs table I am inner joining

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;

Applications table I am updating

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;

我正在尝试对更新查询进行内部联接但是我的语法出错了，据我所知它看起来很好（我可能会查看某些内容）我在这篇文章中列出了所有表格。 p>

我的错误 strong> p> \ n

警告：PDOStatement :: execute（）：SQLSTATE [42000]：语法错误或访问冲突：1064 SQL语法中有错误; 检查与你的MySQL服务器版本相对应的手册，以便在'FROM applications INNER JOIN jobs ON applications.app' p> blockquote>

我的SQL strong> p>
$ 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“; code> pre> 作业表我是内在的加入 strong> p> CREATE TABLE IF NOT EXISTS`jobs`（ `job_id`int（11）NOT NULL， `work_name` varchar（100）NOT NULL， `work_description`文本NOT NULL， `work_duration` int（11）NOT NULL， `work_country` varchar（100）NOT NULL， `work_category` int（50）NOT NULL， job_user` int（100）NOT NULL， `work`employer` varchar（100）NOT NULL， `work_enabled` int（2）NOT NULL DEFAULT'1'， `work_startdate` date NOT NUL L ）ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = latin1; code> pre> 应用程序表我正在更新 strong> p> CREATE TABLE IF NOT NOT EXISTS`applications`（ `in application_id` int（11）NOT NULL， `in application_user` varchar（100）NOT NULL， `in application_date` datetime NOT NULL， \ _`application_job` int（11）NOT NULL， `inapplication_status` int（11）DEFAULT'0'， `in application_enabled` int（2）NOT NULL DEFAULT'1' ）ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = latin1; code> pre> div>

答

MySQL doesn't support FROM clause in Update Statement. Unlike SQL Server, your join statement needs to be before SET as well. You could simply do this instead:

$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"

警告：PDOStatement :: execute（）：SQLSTATE [42000]：语法错误或访问冲突：1064

相关推荐