MySQL 将列更新为始终为 NULL

Question

我有两张表，一张基本上是主表，另一张是供开发人员使用的表。有时我会从开发人员表中复制审查记录并将其移动到主表中:

--显示创建表文档--

CREATE TABLE `document` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(1000) CHARACTER SET utf8 NOT NULL,
  `title` varchar(1000) CHARACTER SET utf8 NOT NULL,
  `content` longtext CHARACTER SET utf8,
  `source_type` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `date_crawled` datetime DEFAULT NULL,
  `mime_type` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `type` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `date_posted` datetime DEFAULT NULL,
  `tier` int(11) DEFAULT NULL,
  `html_content` longtext CHARACTER SET utf8,
  `dev` varchar(255) CHARACTER SET utf8 NOT NULL,
  `dev_document_id` int(11) NOT NULL,
  `promote` int(11) DEFAULT NULL,
  `domain` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`,`url`(500),`dev`,`dev_document_id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB AUTO_INCREMENT=628871 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

--SHOW CREATE TABLE DOCUMENT_DEVELOPER--
CREATE TABLE `document_developer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(1000) CHARACTER SET utf8 NOT NULL,
  `title` varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  `content` longtext CHARACTER SET utf8,
  `jurisdiction` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `source_type` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `date_crawled` datetime DEFAULT NULL,
  `mime_type` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `type` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `html_content` longtext CHARACTER SET utf8,
  `date_posted` datetime DEFAULT NULL,
  `tier` int(11) DEFAULT NULL,
  `promote` int(11) NOT NULL,
  `modified` tinyint(1) NOT NULL DEFAULT '0',
  `date_modified` datetime DEFAULT NULL,
  UNIQUE KEY `url_promote_UNIQUE` (`url`,`promote`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=637067 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY LIST (promote)
(PARTITION part0 VALUES IN (-5) ENGINE = InnoDB,
 PARTITION part1 VALUES IN (-4) ENGINE = InnoDB,
 PARTITION part2 VALUES IN (-3) ENGINE = InnoDB,
 PARTITION part3 VALUES IN (-2) ENGINE = InnoDB,
 PARTITION part4 VALUES IN (-1) ENGINE = InnoDB,
 PARTITION part5 VALUES IN (0) ENGINE = InnoDB,
 PARTITION part6 VALUES IN (1) ENGINE = InnoDB,
 PARTITION part7 VALUES IN (2) ENGINE = InnoDB) */

复制完成后，dev_document_id 列将成为 document_developer 中记录的 id。

但是，当我尝试运行更新时，如下所示:

UPDATE document as d INNER JOIN document_developer AS dd ON d.dev_document_id = dd.id 
SET
d.content=dd.content
WHERE dd.modified='1' AND dd.promote='2';

它总是会给我错误的内容值，例如 NULL NULL NULL ...任何帮助将不胜感激。

Answer 1

您的查询应该是

UPDATE document as d INNER JOIN document_developer AS da ON   
d.dev_document_id = da.id 
SET
d.content=da.content
WHERE da.modified='1' AND da.promote='2';