问题描述
我正在尝试使用单个语句执行多个插入以实现此目的我使用 Insert into select 语句.但是当两列在插入中具有相同的值时，我面临着.我收到的错误消息是 ORA-00918: column ambiguously defined.
I am trying to execute multiple insert with single statement to achieve this I am using Insert into select statement. But I am facing when two columns have same value in insert. Error message that I am getting is ORA-00918: column ambiguously defined.
查询
INSERT INTO sample (
HOST,
TOTAL_PING,
TOTAL_UNAVAILABLE_PING
)
SELECT * FROM (
SELECT 'FR3158-73-1',
82,
82
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
)
问题出现在第一个 select 语句中，其中两个值是 82 和 82，如果我将一个值更改为某些有效值.即使列值相同，我也不知道如何进行这项工作.
Issue is there in first select statement where two values are 82 and 82, if I change one value to something works. I don't know how to make this work even if column values are same.
--- 更新---
表格定义
CREATE TABLE sample
(
ID NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) PRIMARY KEY,
HOST VARCHAR2(15 BYTE),
TOTAL_PING INTEGER,
TOTAL_UNAVAILABLE_PING INTEGER,
ADDED_ON TIMESTAMP(6) DEFAULT systimestamp
);
推荐答案
在这种情况下，您不需要子查询 - 正如@Littlefoot 所示.但是如果你这样做了，在更复杂的场景中，你可以通过在子查询中给列表达式添加别名来避免错误:
In this case you don't need the subquery - as @Littlefoot showed. But if you did, with a more complicated scenario, you can avoid the error by aliasing the column expressions in the subquery:
INSERT INTO sample (
HOST,
TOTAL_PING,
TOTAL_UNAVAILABLE_PING
)
SELECT * FROM (
SELECT 'FR3158-73-1' as host,
82 as total_ping,
82 as total_unavailable_ping
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
)
/
2 rows inserted.
问题在于子查询本身获得隐含的列别名，从查询的第一个分支中的值派生:
The problem is that the subquery on its own gets implied column aliases, derived from the values in the first branch of the query:
SELECT 'FR3158-73-1',
82,
82
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
'FR3158-73- 82 82
----------- ---------- ----------
FR3158-73-1 82 82
FR3158-76-2 80 10
第二列和第三列都称为"82"，这是ORA-00918 抱怨的歧义，来自外部select.如果您添加消失的别名:
The second and third columns are both called "82", which is the ambiguity the ORA-00918 is complaining about, from the outer select. If you add aliases that goes away:
SELECT 'FR3158-73-1' as host,
82 as total_ping,
82 as total_unavailable_ping
FROM DUAL
UNION ALL
SELECT 'FR3158-76-2',
80,
10
FROM DUAL
HOST TOTAL_PING TOTAL_UNAVAILABLE_PING
----------- ---------- ----------------------
FR3158-73-1 82 82
FR3158-76-2 80 10
所以外部查询不再混淆.请注意，您只需要联合的第一个分支中的别名(通常情况下，无论如何) - 这没有什么坏处将它们放在所有分支中，它们只会被忽略，但如果您手动创建它，它会节省一些输入.在这种情况下，实际别名也无关紧要，它们只是必须是唯一的；具体来说，它们不必与您要插入的列匹配 - 但如果它们匹配，则更容易遵循.
so the outer query is no longer confused. Note that you only need the aliases in the first branch of the union (usually, anyway) - it doesn't hurt to have them in all branches, they'll just be ignored, but it saves a bit of typing if you're creating this manually. The actual alias names also don't matter in this case, they just have to be unique; specifically, they don't have to match the columns you're inserting into - but it makes it easier to follow if they do.
如果你像@Littlefoot 所示那样做，你没有中间结果集select，所以不需要评估派生名称(如果它们可以说是存在的话))，所以没有看到歧义 - 这纯粹是位置问题.
If you do it as @Littlefoot showed you don't have the intermediate result set select, so the derived names don't need to be evaluated (if they can be said to exist at all), so the ambiguity is not seen - it's purely positional.
这篇关于Oracle 12c - 插入到选择查询中的不明确列，ORA-00918的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！