mySQL 根据另一个表的多行更新列

Question

我有两个表，dma_projects 和 projectsteps:

dma_projects 具有以下字段: 项目ID 项目名 项目说明

CREATE TABLE IF NOT EXISTS `dma_projects` (
  `projectID` int(11) NOT NULL DEFAULT '0',
 `projectName` varchar(100) DEFAULT NULL,
 `projectDescription` text,
 `projectImage` varchar(255) DEFAULT NULL,
 `projectThumb` varchar(255) DEFAULT NULL,
 `projectCategory` varchar(50) DEFAULT NULL,
  `projectTheme` varchar(50) DEFAULT NULL,
  `projectInstructions` text,
  `projectAuthorID` int(11) DEFAULT NULL,
  `projectViews` int(11) DEFAULT NULL,
 `projectDifficulty` varchar(20) DEFAULT NULL,
 `projectTimeNeeded` varchar(40) DEFAULT NULL,
 `projectDateAdded` int(11) DEFAULT NULL,
 `projectStatus` varchar(20) DEFAULT NULL,
 `projectVisible` varchar(1) DEFAULT NULL,
 PRIMARY KEY (`projectID`),
 KEY `user_id` (`projectAuthorID`),
 KEY `date` (`projectDateAdded`),
 FULLTEXT KEY `image` (`projectImage`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

项目步骤有: 步骤ID 斯特普诺 步进描述 项目ID

CREATE TABLE IF NOT EXISTS `projectsteps` (
  `projectStep_id` int(11) NOT NULL AUTO_INCREMENT,
  `stepno` int(11) DEFAULT '0',
  `stepdesc` text CHARACTER SET utf8 COLLATE utf8_bin,
  `project_id` int(11) NOT NULL,
  PRIMARY KEY (`projectStep_id`)
)

我想使用在projectsteps 表中找到的具有相同projectID 的任何行的值来更新dma_projects.projectInstructions。

即

dma_projects 中的projectID 1 在projectsteps 中有5 条记录，这5 条记录的stepdesc 应连接起来(用
分隔)，然后更新到dma_projects 表的projectInstructions 字段。

我对如何编写查询感到摸不着头脑。这是我到目前为止所处的位置，但我无法让它工作。它说的错误是:

 Unknown column 'projectsteps.stepno' in 'field list'

这是查询:

UPDATE `dma_projects` t1
SET t1.`projectInstructions` = 
(
SELECT 
        `projectsteps`.`stepno`, 
        group_concat(`projectsteps`.`stepdesc` separator '<br/>')
        FROM `projectsteps`

AS somevar
INNER JOIN `projectsteps` t2
ON t1.projectID=t2.project_id
ORDER BY t2.stepno ASC
)   

Answer 1

已更新

UPDATE dma_projects p JOIN
(
  SELECT project_id, GROUP_CONCAT(CONCAT('<li>', stepdesc, '</li>') SEPARATOR '') instructions
    FROM
  (
  SELECT project_id, stepdesc
    FROM projectsteps
   ORDER BY project_id, stepdesc
  ) a
   GROUP BY project_id
) d ON d.project_id = p.projectid 
   SET p.projectInstructions = d.instructions

示例输出:

| PROJECTID | PROJECTNAME |                                          PROJECTINSTRUCTIONS |
------------------------------------------------------------------------------------------
|         1 |    project1 | <li>step11</li><li>step12</li><li>step13</li><li>step14</li> |
|         2 |    project1 | <li>step21</li><li>step22</li><li>step23</li>                |

这里是SQLFiddle 演示