ERROR 1052 (23000): Column in field list is ambiguous

What it means

When a query reads from more than one table, each column name is looked up in all of them. If two tables both have an id (or created_at, name, status…) and the query writes only id, the server can’t know which one you mean, so it refuses the query rather than guess.

The message says where the bare name is. MySQL and MariaDB word it differently:

Common causes

1. Joining tables that both have id, then selecting or filtering on id.
2. Audit columns such as created_at, updated_at or deleted_at on every table, used bare in WHERE or ORDER BY.
3. A new column added to one table that now shares its name with a column of another table in an existing query. The query worked yesterday and breaks after a migration.
4. JOIN … USING (col) merges only the columns named in USING; any other shared name is still ambiguous.
5. A multi-table UPDATE that sets a bare column name.

How to fix it

Prefix the column with its table or alias

Give each table a short alias and use it everywhere the name is shared:

SELECT o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at > '2026-01-01'
ORDER BY o.created_at;

Once a table has an alias, refer to it by the alias: orders.id after FROM orders o is an unknown column. Prefixing every column in a join, even unique ones, keeps the query working when someone adds a column later.

Name both when you want both

SELECT * from a join returns both id columns, and many client libraries keep only the last one under that name. Select and rename them:

SELECT o.id AS order_id, u.id AS user_id, u.name FROM orders o JOIN users u ON u.id = o.user_id;

In ORDER BY and GROUP BY, a select-list alias also works

If the select list already picks one of the columns, the bare name in ORDER BY or GROUP BY resolves to that one. In the test below, SELECT o.id, u.created_at … ORDER BY created_at and … GROUP BY id both ran. WHERE doesn’t look at the select list, so there the prefix is needed.

Multi-table UPDATE

Name the table in SET too:

UPDATE orders o JOIN users u ON u.id = o.user_id
SET o.created_at = NOW()
WHERE u.name = 'Ann';

Reproduce it

On MySQL 8.4.11, with orders2 (id, user_id, created_at) and users2 (id, name, created_at):

SELECT id, name FROM orders2 JOIN users2 ON users2.id = orders2.user_id;
SELECT o.id FROM orders2 o JOIN users2 u ON u.id = o.user_id WHERE created_at > '2026-01-01';
SELECT o.id FROM orders2 o JOIN users2 u ON u.id = o.user_id ORDER BY created_at;
SELECT * FROM orders2 o JOIN users2 u USING (created_at) WHERE id = 1;
UPDATE orders2 o JOIN users2 u ON u.id = o.user_id SET created_at = NOW();

ERROR 1052 (23000): Column 'id' in field list is ambiguous
ERROR 1052 (23000): Column 'created_at' in where clause is ambiguous
ERROR 1052 (23000): Column 'created_at' in order clause is ambiguous
ERROR 1052 (23000): Column 'id' in where clause is ambiguous
ERROR 1052 (23000): Column 'created_at' in field list is ambiguous

JOIN … USING (id) WHERE id = 1 ran, because USING merges id into one column.

MariaDB 11.4.13 gave the same number and SQLSTATE in the same places, with its own wording:

ERROR 1052 (23000): Column 'id' in SELECT is ambiguous
ERROR 1052 (23000): Column 'created_at' in WHERE is ambiguous
ERROR 1052 (23000): Column 'created_at' in ORDER BY is ambiguous
ERROR 1052 (23000): Column 'id' in WHERE is ambiguous
ERROR 1052 (23000): Column 'created_at' in SET is ambiguous

In Inlet

Inlet’s query editor completes table and column names from the live schema, and the formatter lays a join out one clause per line, which makes a bare column easier to spot. When the query fails, Inlet shows the error and links to this page; with your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the statement from the schema.