MySQL insert语句(insert into table (列) select语句)

Question

我尝试将值插入到表的选定列中，但只插入MySQL语句的结果。select语句本身工作正常，并返回结果。

当与insert语句结合使用时，它将失败，并显示

error incorrect syntax near `dedspgoods`.`case number`.

有人能帮助我正确的语法吗？我的错误语法如下：

insert into despgoods_alldetails 
  (`case number`, weight, customer, transporttypename) 
values 
( select despgoods.`case number`
       , despgoods.weight
       , despgoods.customer
       , customers.transporttypename 
  from despgoods 
    inner join customers 
      on despgoods.customer = customers.customer )

Answer 1

如果这是有效的SELECT：

select despgoods.`case number`
     , despgoods.weight
     , despgoods.customer
     , customers.transporttypename 
from despgoods 
  inner join customers 
    on despgoods.customer = customers.customer

然后尝试这样做(请注意，如果要插入SELECT的结果集，则没有VALUES子句

insert into despgoods_alldetails 
  (`case number`, weight, customer, transporttypename) 
select despgoods.`case number`
     , despgoods.weight
     , despgoods.customer
     , customers.transporttypename 
from despgoods 
  inner join customers 
    on despgoods.customer = customers.customer

Answer 2

正如@EdHeal所述，由于列名中有一个空格，因此需要将case number括在引号或反引号之间( CASE是保留字的事实实际上只是一个有趣的细节，但这并不是本例中破坏查询的原因)。

insert into despgoods_alldetails (`case number`,weight,customer,transporttypename) 
values (
    select despgoods.`case number`, despgoods.weight, despgoods.customer, customers.transporttypename
    from despgoods 
    inner join customers on (despgoods.customer=customers.customer)
)

Answer 3

case是保留字。我需要将‘案例编号’放在引号中。