I am trying to copy the data from online_class:class_category to categories:uuid,title

Table: online_class

Table: categories

INSERT INTO `categories` (`uuid`, `title`) 
    SELECT 
        (SELECT UUID()) AS `uuid`,
        DISTINCT(`class_category`) AS `title`
    FROM `online_class`

Getting this error

Error Code: 1064
You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax 
to use near 'DISTINCT(`class_category`) as `title`
    FROM `online_class`' at line 4

What's wrong in this query

CodePudding user response：

I found another way to do this.

INSERT INTO `categories` (`uuid`, `title`) 
    SELECT UUID(), `class_category` FROM `online_class`
    GROUP BY `class_category`

CodePudding user response：

Try to put DISTINCT with field in the first position after SELECT, such as:

INSERT INTO `categories` (`title`, `uuid`) 
    SELECT 
        DISTINCT(`class_category`) AS `title`,
        (SELECT UUID()) AS `uuid`
    FROM `online_class`

The document shows the grammar, DISTINCT follows SELECT :

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition] .....

Ref : https://dev.mysql.com/doc/refman/8.0/en/select.html