MySQL - UPDATE one column based on results of a SELECT when the SELECT returns multiple columns

Question

I've read MySQL - UPDATE query based on SELECT Query and am trying to do something similar - i.e. run an UPDATE query on a table and populate it with the results from a SELECT.

In my case the table I want to update is called substances and has a column called cas_html which is supposed to store CAS Numbers (chemical codes) as a HTML string.

Due to the structure of the database I am running the following query which will give me a result set of the substance ID and name (substances.id, substances.name) and the CAS as a HTML string (cas_values which comes from cas.value):

SELECT s.`id`, GROUP_CONCAT(c.`value` ORDER BY c.`id` SEPARATOR '<br>') cas_values, GROUP_CONCAT(s.`name` ORDER BY s.`id`) substance_name FROM substances s LEFT JOIN cas_substances cs ON s.id = cs.substance_id LEFT JOIN cas c ON cs.cas_id = c.id GROUP BY s.id;

Sample output:

id   |   cas_values   |   substance_name
----------------------------------------
1    |   133-24<br>   |   Chemical A
         455-213<br>
         21-234
-----|----------------|-----------------
2        999-23       |   Chemical B
-----|----------------|-----------------
3    |                |   Chemical C
-----|----------------|-----------------

As you can see the cas_values column contains the HTML string (which may also be an empty string as in the case of "Chemical C"). I want to write the data in the cas_values column into substances.cas_html. However I can't piece together how to do this because other posts I'm reading get the data for the UPDATE in one column - I have other columns returned by my SELECT query.

Essentially the problem is that in my "sample output" table above I have 3 columns being returned. Other SO posts seem to have just 1 column being returned which is the actual values that are used in the UPDATE query (in this case on the substances table).

Is this possible?

I am using MySQL 5.5.56-MariaDB

These are the structures of the tables, if this helps:

    mysql> DESCRIBE substances;
    +-------------+-----------------------+------+-----+---------+----------------+
    | Field       | Type                  | Null | Key | Default | Extra          |
    +-------------+-----------------------+------+-----+---------+----------------+
    | id          | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | app_id      | varchar(8)            | NO   | UNI | NULL    |                |
    | name        | varchar(1500)         | NO   |     | NULL    |                |
    | date        | date                  | NO   |     | NULL    |                |
    | cas_html    | text                  | YES  |     | NULL    |                |
    +-------------+-----------------------+------+-----+---------+----------------+
    4 rows in set (0.01 sec)

    mysql> DESCRIBE cas;
    +-------+-----------------------+------+-----+---------+----------------+
    | Field | Type                  | Null | Key | Default | Extra          |
    +-------+-----------------------+------+-----+---------+----------------+
    | id    | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
    | value | varchar(13)           | NO   | UNI | NULL    |                |
    +-------+-----------------------+------+-----+---------+----------------+
    2 rows in set (0.01 sec)

    mysql> DESCRIBE cas_substances;
    +--------------+-----------------------+------+-----+---------+----------------+
    | Field        | Type                  | Null | Key | Default | Extra          |
    +--------------+-----------------------+------+-----+---------+----------------+
    | id           | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
    | cas_id       | mediumint(8) unsigned | NO   | MUL | NULL    |                |
    | substance_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
    +--------------+-----------------------+------+-----+---------+----------------+
    3 rows in set (0.02 sec)

I've added it to the description of `substances` table, which is where it'll go. To be honest I haven't added that column in the database yet as I don't know whether what I'm trying to do in this question will be possible. — Andy, Feb 20 '18 at 10:48

Daniel E. · Accepted Answer · 2018-02-20T13:20:44.933

2

Try something like this :

UPDATE substances AS s,
(
SELECT s.`id`, 
GROUP_CONCAT(c.`value` ORDER BY c.`id` SEPARATOR '<br>') cas_values,
GROUP_CONCAT(s.`name` ORDER BY s.`id`) substance_name
FROM substances s
LEFT JOIN cas_substances cs ON s.id = cs.substance_id
LEFT JOIN cas c ON cs.cas_id = c.id    
GROUP BY s.id
) AS t
SET s.cas_html=t.cas_values
WHERE s.id = t.id

If you don't want to modify all the value, the best way to limit the update to test it, is to add a condition in the where, something like that :

... 
WHERE s.id = t.id AND s.id = 1

edited Feb 20 '18 at 13:20

answered Feb 20 '18 at 11:01

Daniel E.

2,440
1
14
24

Thanks. I tried adding `LIMIT 1` to the end of the query to test it but it said "Incorrect usage of UPDATE and LIMIT". In the end I just ran it and it took approx 3.6 seconds to update 236142 rows. Looks good. Incidentally do you know if adding a `LIMIT` is possible for this type of query - e.g. to test on the first n-records? – Andy Feb 20 '18 at 11:06
Where do you want to add the limit ? – Daniel E. Feb 20 '18 at 11:07
Well I don't know that's what I'm asking. The intention was to update, for example, the first 1 record (`LIMIT 1`) of the `substances` table to see the result before updating all of the other records. As it happens they've all been updated anyway. I'm curious to know if this is possible so someone could preview the results before running the query on the rest of the table. – Andy Feb 20 '18 at 11:27
You can't in the update as far as i know, I edit my answer to explain a bit more – Daniel E. Feb 20 '18 at 13:19

MySQL - UPDATE one column based on results of a SELECT when the SELECT returns multiple columns

1 Answers1