我使用的查询如下
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