MySQL Update set

Question

MySQL Update set

Hello everyone. Such a task:

UPDATE `table` SET `colum` = (SELECT * FROM `table2 WHERE visible = 1 LIMIT 0,1) WHERE `id` = 12`

Everything works as long as it returns one response. If you remove LIMIT starts swearing that the answer is not one. Is it possible to somehow turn the response into a string. To work without LIMIT (please don't ask why I need it)

1

mysql phpmyadmin crud

Author: Fariz Mamedow, 2019-06-29

Source

1 answers

score 0 · Accepted Answer

Since it is necessary to concatenate a field of several lines into a single value (I will take it for granted that this field is also text and is called name), the function is usefulGROUP_CONCAT:

UPDATE `table` SET `colum` = (
  SELECT group_concat(name) FROM `table2 
  WHERE visible = 1
) WHERE `id` = 12

If no other separator is specified, the string values will be separated by a comma. Another separator can be specified with the syntax

group_concat(name SEPARATOR ' ')

Note that group_concat has an upper limit on the size of the resulting string: it is defined by the group_concat_max_len variable. If you exceed this limit the size of the string will be truncated at the end.