mySQL update column based on multiple rows of another table

Question

I have two tables, dma_projects and projectsteps:

dma_projects has the following fields: projectID projectName projectInstructions

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;

projectsteps has: stepID stepno stepdesc project_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`)
)

I want to update dma_projects.projectInstructions with the values of any rows that are found in the projectsteps table that have the same projectID.

I.e.

projectID 1 in dma_projects has 5 records in projectsteps, the stepdesc of those five records should be joined (separated by a
) and then updated to the projectInstructions field of the dma_projects table.

I'm scratching my head as how to write the query. Here is where I am so far but I can't get it working. The error it says is:

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

Here is the query:

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
)

are you sure there is column stepno?

igor
– igor

2013-06-07 05:55:23 +00:00
Commented Jun 7, 2013 at 5:55 — igor
– igor, Commented Jun 7, 2013 at 5:55
Yes, it's definitely there

Amo
– Amo

2013-06-07 05:57:44 +00:00
Commented Jun 7, 2013 at 5:57 — Amo
– Amo, Commented Jun 7, 2013 at 5:57

peterm · Accepted Answer · 2013-06-07 06:20:19Z

1

UPDATED

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

Sample output:

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

Here is SQLFiddle demo

edited Jun 7, 2013 at 6:20

answered Jun 7, 2013 at 6:08

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Amo Over a year ago

That is perfect! Exactly what I needed. On a related note, at the moment I'm using the <br/> separator. What would be ideal is to have </li> as a separator and then prepend a <li> to the beginning of the field. Do you know if that'd be something I can achieve?

Amo Over a year ago

In fact, I've managed to figure out how to do just that! I just changed the code to SELECT project_id, GROUP_CONCAT(stepdesc SEPARATOR '</li><li>') instructions and SET p.projectInstructions = concat(concat('<li>',d.instructions),'</li>') on the last line. Here is a SQLFiddle demo sqlfiddle.com/#!2/9f1c36/1/0

Luis Over a year ago

When would it take to update approximately 20 thousand records?

Collectives™ on Stack Overflow

mySQL update column based on multiple rows of another table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related