我使用的查询如下

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

我明白了
错误代码:1060
列名“NA”重复
我的问题是不能在数据库表的两列中添加相同的数据。

最佳答案

当您选择常量值而不命名列时,如下所示

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

10-06 05:55
查看更多