1

I have a MySQL database. I want to update a column(in my case title column in bms_title table) in a table using the values from concat columns in other tables.

SELECT * FROM(SELECT distinct t.id, t.title as Textbook,
GROUP_CONCAT(concat(ci.discipline_code, ci.code, " (" , ci.type , ")") SEPARATOR ', ') as CourseCode FROM 
tms_local.bms_material m, 
tms_local.bms_title t, 
tms_local.bms_course c,
tms_local.bms_courseinfo ci
where t.id > 1 AND t.id = m.book_id 
and c.id = m.course_id 
and ci.id = c.id
and  isbn != 'NA'
GROUP BY t.id) AS temporary_table;

UPDATE tms_local.bms_title
SET tms_local.bms_title.thumbnail = temporary_table.CourseCode
WHERE tms_local.bms_title.title=temporary_table.Textbook;

But I got the error: Unknow temporary_table.Textbook in where clause.

How could I update the tms_local.bms_title.thumbnail column using CourseCode column from the selected table?

enter image description here

I have tried

CREATE TEMPORARY TABLE IF NOT EXISTS temporary_table AS (SELECT distinct t.id, t.title as Textbook,
GROUP_CONCAT(concat(ci.discipline_code, ci.code, " (" , ci.type , ")") SEPARATOR ', ') as CourseCode FROM 
tms_local.bms_material m, 
tms_local.bms_title t, 
tms_local.bms_course c,
tms_local.bms_courseinfo ci
where t.id > 1 AND t.id = m.book_id 
and c.id = m.course_id 
and ci.id = c.id
and  isbn != 'NA'
GROUP BY t.id);

UPDATE tms_local.bms_title
SET tms_local.bms_title.thumbnail = temporary_table.CourseCode
WHERE tms_local.bms_title.title=temporary_table.Textbook;

But got the same error.

django
  • 43
  • 4
  • Does this answer your question? [MySQL Update Inner Join tables query](https://stackoverflow.com/questions/8057565/mysql-update-inner-join-tables-query) – ggordon Oct 10 '21 at 15:02
  • no, I just want to know how to use the column in the selected table for the following SQL – django Oct 10 '21 at 15:03
  • 1
    Does this answer your question? [Create a temporary table in a SELECT statement without a separate CREATE TABLE](https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table) – Luuk Oct 10 '21 at 15:05
  • temporary_table wont exist in your update statement. Youll probably need to do a `SELECT` within your `UPDATE` statement. Or do what @Luuk just linked – slashroot Oct 10 '21 at 15:05
  • @django did you get an error for the temporary table being created or only an error for the `UPDATE` statement after? After TEMPORARY TABLE creation are you able to SELECT from temporary_table? – slashroot Oct 10 '21 at 15:19

2 Answers2

0

you need to join the select statement.

As seen below:

UPDATE tms_local.bms_title t0
        INNER JOIN
    (SELECT 
        *
    FROM
        (SELECT DISTINCT
        t.id,
            t.title AS Textbook,
            GROUP_CONCAT(CONCAT(ci.discipline_code, ci.code, ' (', ci.type, ')')
                SEPARATOR ', ') AS CourseCode
    FROM
        tms_local.bms_material m, tms_local.bms_title t, tms_local.bms_course c, tms_local.bms_courseinfo ci
    WHERE
        t.id > 1 AND t.id = m.book_id
            AND c.id = m.course_id
            AND ci.id = c.id
            AND isbn != 'NA'
    GROUP BY t.id) AS temporary_table) t1 ON t0.title = t1.Textbook 
SET 
    t0.thumbnail = t1.ourseCode;
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Got some warning said 1265 Data truncated for column 'thumbnail' at row XXX – django Oct 10 '21 at 15:13
  • then the datatypes don't fit together, you didn't provide data, and structure so i can't reproduce your error- – nbk Oct 10 '21 at 15:17
  • to explain you r error message ourseCode has more characters than thumbnail can hold – nbk Oct 10 '21 at 15:31
0

Your temporary_table is being lost between the first statement and the second.

I find the WITH ... AS SQL structure to be helpful to get these together and is far more readable:

WITH temporary_table AS(
    SELECT * 
    FROM(SELECT distinct t.id, 
                t.title as Textbook,
                GROUP_CONCAT(concat(ci.discipline_code, 
                                    ci.code, 
                                    " (" , 
                                    ci.type ,
                                    ")") 
                             SEPARATOR ', '
                            ) as CourseCode 
         FROM tms_local.bms_material m, 
              tms_local.bms_title t, 
              tms_local.bms_course c,
              tms_local.bms_courseinfo ci
         WHERE t.id > 1 AND t.id = m.book_id 
                    and c.id = m.course_id 
                    and ci.id = c.id
                    and  isbn != 'NA'
         GROUP BY t.id)
UPDATE tms_local.bms_title
SET tms_local.bms_title.thumbnail = temporary_table.CourseCode
WHERE tms_local.bms_title.title=temporary_table.Textbook;
madtyn
  • 1,469
  • 27
  • 55