// MySQL error reference

MySQL error 1060: Duplicate column name

A table definition, ALTER TABLE or a view or derived table produces two columns with the same name.

Error number
1060
Name
ER_DUP_FIELDNAME
SQLSTATE
42S21
Category
Databases, tables and columns
Error message
Duplicate column name '%s'

What it means

Column names must be unique within a table, and also within the result of a view or derived table. 1060 names the duplicate. It often comes from SELECT * over a join where both tables have an id column, used to create a table or view.

Common causes

  • ALTER TABLE ... ADD COLUMN for a column that already exists (a migration run twice).
  • CREATE TABLE ... AS SELECT * or CREATE VIEW over a join with columns of the same name.
  • A derived table that selects two columns with the same name.

How to fix it

  1. Give duplicate columns distinct aliases.

    CREATE VIEW order_view AS
    SELECT o.id AS order_id, c.id AS customer_id, c.name
    FROM orders o JOIN customers c ON c.id = o.customer_id;
  2. Check whether the column already exists before adding it.

    SHOW COLUMNS FROM orders LIKE 'shipped_at';

Related errors

More databases, tables and columns errors

The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.

// fewer errors, faster fixes

Work on MySQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.