mysql - 错误代码 : 1060 Duplicate column name 'NA'

Question

我使用的查询如下

INSERT INTO `tbl_contact` (`fname`,`code`,`contact`,`m_address`,`created_date`,`modified_date`,`row_status`) 
SELECT * FROM (SELECT 'LULU CENT','sdfjg','NA','NA',1991-01-01,1990-01-01,'active') 
AS tmp WHERE NOT EXISTS (SELECT `id` FROM tbl_contact WHERE CODE='sdfjg') LIMIT 1

我得到以下信息

Error Code: 1060 Duplicate column name 'NA'

我的问题是我无法在数据库表的两列中添加相同的数据。

Answer 1

当您选择常量值而不像下​​面这样命名列时

SELECT 'LULU CENT','sdfjg','NA'

返回结果的列名将与对应的列值相同，即第一列名是LULU CENT，第二列名是sdfjg 等等。

因为你的子查询如下

SELECT 'LULU CENT','sdfjg','NA','NA',1991-01-01,1990-01-01,'active'

第三和第四列名称将相同 (NA)。如果将上述查询放在 SELECT * FROM (...) AS tmp 中，您将收到 1060 错误，如下所示

SELECT * FROM 
(
    SELECT 'LULU CENT',
    'sdfjg',
    'NA',
    'NA',
    1991-01-01,
    1990-01-01,
    'active'
) 
AS tmp

您需要为子查询中的每一列设置不同的名称，如下所示

INSERT INTO `tbl_contact` 
(
    `fname`,
    `code`,
    `contact`,
    `m_address`,
    `created_date`,
    `modified_date`,
    `row_status`
) 
SELECT * FROM 
(
    SELECT 
        'LULU CENT' AS `fname`,
        'sdfjg' AS `code`,
        'NA' AS `contact`,
        'NA' AS `m_address`,
        1991-01-01 AS `created_date`,
        1990-01-01 AS `modified_date`,
        'active' AS `row_status`
) 
AS tmp WHERE NOT EXISTS (SELECT `id` FROM tbl_contact WHERE CODE='sdfjg') LIMIT 1