MySQL 插入语句（插入表（列）选择语句）

MySQL insert statement (insert into table (columns) select statement)

提问by Smudger

I am trying to insert values into selected columns of table, but only insert the results of a MySQL statement. the select statement works correctly by itself and returns the results.

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

when combined with the insert statement it is fails with

当与插​​入语句结合时，它会失败

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

Can anyone assist me with the correct syntax? my erronous syntax is as below:

任何人都可以帮助我使用正确的语法吗？我的错误语法如下：

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 )

回答by ypercube??

If this is the SELECTthat works:

如果这是SELECT有效的：

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

Then try this (notice there is no VALUESclause if you want to insert the result set of a SELECT:

然后试试这个（请注意，VALUES如果要插入 a 的结果集，则没有子句SELECT：

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

回答by Romain

As stated by @EdHeal, you'll need to enclose case numberbetween quotes or backticks as there is a space in the column name (the fact that CASEis a reserved word is actually only an interesting detail, but that's not what breaks the query in this case).

正如@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)
)

回答by Ed Heal

caseis a reserved word. Need to put 'case number' in quotes.

case是保留字。需要将“案例编号”放在引号中。

回答by Nathan

here it goes

在这里

 INSERT INTO despgoods_alldetails(
                                  casenumber,
                                  weight,
                                  customer,
                                  transporttyplename)


 VALUES(SELECT desp.casenumber,
        desp.weight,
        despgoods.customer, 
        customers.transporttypename)

 FROM despgoods
 INNER JOIN customers on despgoods.customer = customers.customer

there. that should work fine. Remember, do not name your variables with spaces, because it can get real tricky. I think you had an error in case number because it has a space in between, when case is a function in MySQL. Let me know if you need anything else.

那里。那应该可以正常工作。请记住，不要用空格命名变量，因为它会变得非常棘手。我认为你在 case number 中有一个错误，因为它之间有一个空格，当 case 是 MySQL 中的一个函数时。需要帮助请叫我。