我有此查询:
INSERT INTO tbl_1(id , name)
SELECT id, 'XXX'
FROM tbl_2 t WHERE t.name='XXX';
当Select子句匹配时,它正在工作。我想在" ID"字段中使用0执行插入查询,即使选择不匹配。
您可以使用UNION
,其优点是,"基础"选择的行数不限于一个:
INSERT INTO tbl_1 (id , name)
SELECT id, 'XXX'
FROM tbl_2 t
WHERE t.name='XXX'
union all
select 0, 'XXX'
where not exists (select 1 from tbl_2 t where t.name = 'XXX');
将您的查询包括在子选择中(因此,它将始终返回至少一个值):
INSERT INTO tbl_1(id , name)
SELECT COALESCE((SELECT id FROM tbl_2 t WHERE t.name='XXX'), 0), 'XXX';
或
INSERT INTO tbl_1(id , name)
SELECT COALESCE((SELECT id FROM tbl_2 t WHERE t.name=v.name), 0), v.name
FROM (VALUES ('XXX')) v(name);