ERROR SOLVED – ora-00918: column ambiguously defined

The error “ora-00918: column ambiguously defined” ora-00918: column ambiguously defined occurs when the SQL query references a column name that appears in more than one table or subquery in the FROM clause, and the query doesn’t specify which table or subquery the column should be taken from.

In order to fix this, you will need to qualify the column name with the table or subquery name. For example, instead of using “id” in the SELECT statement, use “table_name.id” or “subquery_name.id” to specify which table or subquery the column should be taken from.

ora-00918: column ambiguously defined explained with example

Another case for this error message is when you are using reserved keywords as column name, you can fix this by using double quotes around the column name.

sql code

CREATE TABLE departments (
    "id" INT PRIMARY KEY,
    "name" VARCHAR(255) NOT NULL,
    "budget" DECIMAL(10,2) NOT NULL
);

Also ensure that you are not trying to create a table that already exists in the database, you can check the existing tables by querying the data dictionary using the following SQL command

sql code

SELECT table_name FROM user_tables;

and if the table exists, you can drop it before creating it again.

sql code

DROP TABLE departments;

Please note that this command may vary depending on the type of SQL database you are using.

I hope this solution will help you run your sql statement without any error.

mahaonline.digital

Scroll to Top