通过从两列中选择值来插入同一表中的一列 [英] insert by selecting values from two columns into a column from the same table

问题描述

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

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

推荐答案

问题:

1. 如Matt所述，您需要在MySql中使用CONCAT()
2. 对于插入的第一条记录，SELECT返回NULL，因此您需要使用COALESCE()或IFNULL()来获取默认值以进行串联

1. As Matt mentioned you need to use CONCAT() in MySql
2. 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

这篇关于通过从两列中选择值来插入同一表中的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！