ORA-00918: 插入多行时列引用不明确

huangapple go评论73阅读模式
英文:

ORA-00918: Column ambiguously defined when inserting multiple rows

问题

It seems you're trying to insert data into a table named guest and are encountering an ORA-00918 error related to the names column. The problem lies in the fact that you are using a common table expression (CTE) named names in your query, which has the same name as a column in your guest table.

To resolve this issue, you should either rename the CTE to something other than names or fully qualify the names column in your SELECT statement by specifying which table it belongs to. Here's an example of renaming the CTE:

  1. INSERT INTO guest (first_name, last_name, address, phone, email, document_id, nationality, status, reservation_id, document_type_id)
  2. WITH guest_data AS (
  3. SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1, 1, 1 FROM dual UNION ALL
  4. SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1, 2, 1 FROM dual UNION ALL
  5. SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1, 3, 1 FROM dual UNION ALL
  6. SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1, 4, 1 FROM dual
  7. )
  8. SELECT * FROM guest_data;

This should help you avoid the column ambiguity issue. Additionally, based on your DDL, the ID column in the guest table is generated automatically, so you don't need to specify it in your INSERT statement.

英文:

I am trying to add multiple rows to a table using this reference, statement 8:

  1. INSERT INTO people (person_id, given_name, family_name, title)
  2. WITH names AS (
  3. SELECT 4, 'Ruth', 'Fox', 'Mrs' FROM dual UNION ALL
  4. SELECT 5, 'Isabelle', 'Squirrel', 'Miss' FROM dual UNION ALL
  5. SELECT 6, 'Justin', 'Frog', 'Master' FROM dual UNION ALL
  6. SELECT 7, 'Lisa', 'Owl', 'Dr' FROM dual
  7. )
  8. SELECT * FROM names

The problem is that it uses a table called names which hasn't been mentioned until that example. I treated it as a placeholder and proceeded with my own query, which is as follows:

  1. INSERT INTO guest (first_name, last_name, address, phone, email, document_id, nationality, status, reservation_id, document_type_id)
  2. WITH names AS (
  3. SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1, 1, 1 FROM dual UNION ALL
  4. SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1, 2, 1 FROM dual UNION ALL
  5. SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1, 3, 1 FROM dual UNION ALL
  6. SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1, 4, 1 FROM dual
  7. )
  8. SELECT * FROM names;

As expected, I get the following error:
SQL Error: ORA-00918: column ambiguously defined. What could be the problem? I guess it has something to do with that names column, but I cannaot figure out what. Here's my DDL for the table I'm trying to insert data to:

  1. CREATE TABLE guest (
  2. id INTEGER
  3. GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 CYCLE CACHE 1000 ORDER )
  4. NOT NULL,
  5. first_name VARCHAR2(50) NOT NULL,
  6. last_name VARCHAR2(50) NOT NULL,
  7. address VARCHAR2(200) NOT NULL,
  8. phone NUMBER,
  9. email VARCHAR2(100),
  10. document_id VARCHAR2(30) NOT NULL,
  11. nationality VARCHAR2(50) NOT NULL,
  12. status INTEGER DEFAULT 1 NOT NULL,
  13. reservation_id INTEGER NOT NULL,
  14. document_type_id INTEGER NOT NULL
  15. )
  16. LOGGING;
  17. ALTER TABLE guest
  18. ADD CONSTRAINT ck_guest_status CHECK ( status BETWEEN 1 AND 5 );
  19. COMMENT ON TABLE guest IS
  20. 'Stores information about guests.';
  21. ALTER TABLE guest ADD CONSTRAINT guest_pk PRIMARY KEY ( id );
  22. ALTER TABLE guest
  23. ADD CONSTRAINT gst_dcmnt_tp_fk FOREIGN KEY ( document_type_id )
  24. REFERENCES document_type ( id )
  25. NOT DEFERRABLE;

In general, I want my table to automatically increment assign an id to the guest id when a row is inserted.

答案1

得分: 8

以下是您要翻译的内容:

问题在于您没有为占位查询中的列指定名称。请尝试:

  1. INSERT INTO guest (first_name, last_name, address, phone, email,
  2. document_id, nationality, status, reservation_id, document_type_id)
  3. WITH names (first_name, last_name, address, phone, email,
  4. document_id, nationality, status, reservation_id, document_type_id) AS (
  5. SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1, 1, 1 FROM dual UNION ALL
  6. SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1, 2, 1 FROM dual UNION ALL
  7. SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1, 3, 1 FROM dual UNION ALL
  8. SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1, 4, 1 FROM dual
  9. )
  10. SELECT * FROM names;

当您不指定任何名称时,Oracle会将列命名为第一行中的值:'JOHN','DOE'等。这会导致您有三列在第一行包含值1,结果为3列命名为'1'的歧义!

此查询说明了Oracle如何命名列:

  1. WITH names AS (
  2. SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1 FROM dual UNION ALL
  3. SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1 FROM dual UNION ALL
  4. SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1 FROM dual UNION ALL
  5. SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1 FROM dual
  6. )
  7. SELECT * FROM names where "'DOE'" = 'Drewton';

结果:

'JOHN' 'DOE' 'GROVESTREET8' 111222333 'JOHNDOE@MAIL.CO 'JFV5R 'ENGLIS 1


Hannah Drewton Elm Street 8 551222333 hannah@mail.com GETER3 English 1
David Drewton Elm Street 8 551225333 dvddrw@mail.com 94TER3 English 1

英文:

The issue here is that you haven't given the columns in your placeholder query any names. Try:

  1. INSERT INTO guest (first_name, last_name, address, phone, email,
  2. document_id, nationality, status, reservation_id, document_type_id)
  3. WITH names (first_name, last_name, address, phone, email,
  4. document_id, nationality, status, reservation_id, document_type_id) AS (
  5. SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1, 1, 1 FROM dual UNION ALL
  6. SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1, 2, 1 FROM dual UNION ALL
  7. SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1, 3, 1 FROM dual UNION ALL
  8. SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1, 4, 1 FROM dual
  9. )
  10. SELECT * FROM names;

When you don't specify any names, Oracle names the columns after the values in the first row: 'JOHN', 'DOE' etc. This causes ambiguity for you because you have 3 columns containing a value 1 in the first row, resulting in 3 columns named 1!

This query illustrates how Oracle is naming the columns:

  1. WITH names AS (
  2. SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1 FROM dual UNION ALL
  3. SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1 FROM dual UNION ALL
  4. SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1 FROM dual UNION ALL
  5. SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1 FROM dual
  6. )
  7. SELECT * FROM names where "'DOE'" = 'Drewton';

Results:

  1. 'JOHN' 'DOE' 'GROVESTREET8' 111222333 'JOHNDOE@MAIL.CO 'JFV5R 'ENGLIS 1
  2. ------ ------- ----------------- ---------- ---------------- ------ ------- ----------
  3. Hannah Drewton Elm Street 8 551222333 hannah@mail.com GETER3 English 1
  4. David Drewton Elm Street 8 551225333 dvddrw@mail.com 94TER3 English 1

huangapple
  • 本文由 发表于 2020年1月7日 01:11:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/59616253.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定