Duplicate Column Name Error in MySQL

This typically happens when you retrieve data from multiple tables with the same column name using a JOIN statement. You might receive an error like this:

ERROR: Column 'col_name' in field list is ambiguous

To avoid this error, you should define the table when inserting the column name in the SQL statement.

Example:

We have table departments and dept_emp with the same column name dept_no.

Now we try to join two tables using the dept_no column:

SELECT
  dept_no,
  dept_name
FROM
  departments
  INNER JOIN dept_emp
WHERE
  departments.dept_no = dept_emp.dept_no;

MySQL throws back the error message:

Query 1 ERROR: Column 'dept_no' in field list is ambiguous

To fix it, specify the tabledepartment for the column dept_no:

SELECT
  departments.dept_no,
  dept_name
FROM
  departments
  INNER JOIN dept_emp
WHERE
  departments.dept_no = dept_emp.dept_no;

Another case when you create a view:

CREATE VIEW order_view AS
SELECT
  customers.id,
  payment.id,
  total
FROM
  customers
  INNER JOIN orders
WHERE
  customers.id = orders.customer_id;

MySQL throws back the error message:

Query 1 ERROR: Duplicate column name 'dept_no'

A view also can’t have two columns of the same name. So to fix this, we can use alias:

CREATE VIEW order_view AS
SELECT
  customers.id,
  payment.id AS order_id,
  total
FROM
  customers
  INNER JOIN orders
WHERE
  customers.id = orders.customer_id;

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.