通过从两列中选择值来插入同一表中的一列

问题描述：

请插入值，方法是从同一表的两列中选择两个值.以下是我的查询.

Please I am inserting values by selecting two values from two columns into one column in the same table.Below are my queries.

create table table1(
 id int(3) zerofill auto_increment primary key,
 prefix varchar(10) default "AB",
 username varchar(10)
)
engine=innodb;

MySQL插入查询

 insert into table1 (username) 
 select prefix + (LPAD(Coalesce(MAX(id),0) + 1,3, '0'))
  from table1;

上面的插入查询不起作用，它在用户名列中提供了null，请提供任何帮助.谢谢. 预期结果如下.

The above insert query does not work,it gives null in the username column,please any help is appreciated.Thanks. The expected results is below.

   id    Prefix   username
   001    AB       AB001
   002    AB       AB002
   003    AB       AB003

答

问题:

As Matt mentioned you need to use CONCAT() in MySql
For the first record being inserted your SELECT returns NULL therefore you need to use COALESCE() or IFNULL() to get DEFAULT value for concatenation

您的查询应如下所示

INSERT INTO table1 (username)
SELECT CONCAT(COALESCE(prefix, 'AB'), LPAD(COALESCE(MAX(id), 0) + 1, 3, '0'))
  FROM table1

结果:


| ID | PREFIX | USERNAME |
--------------------------
|  1 |     AB |    AB001 |
|  2 |     AB |    AB002 |

这里是 SQLFddle

Here is SQLFddle