Fix “Unknown table … in HANDLER” in MySQL (Error 1109)

If you’re getting MySQL error 1109 which reads something like “1109 (42S02): Unknown table ‘products’ in HANDLER” in MySQL, it appears that you’re trying to reference a table that’s not currently open when using MySQL’s HANDLER statement.

This can happen when you try to read from a table that you haven’t yet opened. It can also happen when you’ve assigned an alias to the table but you try to reference it without the alias. And it can happen if you try to close a table that isn’t actually open.

Continue reading

Fix “Column names in each table must be unique…” in SQL Server (Error 2705)

If you’re getting an error that reads something like “Column names in each table must be unique. Column name ‘c1’ in table ‘t1’ is specified more than once” in SQL Server, it appears that you’re trying to create a table with duplicate column names, or you’re trying to add a column to a table that already has a column of that name.

Column names must be unique within each table.

Continue reading

Possible Reason You’re Getting Error 156 When Running a Subquery in SQL Server

Error 156 in SQL Server is a generic error that doesn’t tell us much, other than the fact that we’re using the wrong syntax.

But if you’re running a subquery, and you’re getting error 156, which reads “Incorrect syntax near the keyword ‘WHERE’.” or whatever keyword it’s near, it could be that you haven’t declared an alias for the subquery.

Continue reading

Why You Might be Getting the 4104 Error When Using the OUTPUT Clause in SQL Server

You may be familiar with SQL Server error 4104 that reads something like “The multi-part identifier “DELETED.Name” could not be bound“, which tells us that the “multi-part identifier” couldn’t be bound.

You may have seen this error when performing joins across tables when using the wrong table prefix for a column or using the table name instead of its alias. But that’s not the only place we can get this error.

If you’re getting this error while using the OUTPUT clause (which we can use during INSERT, UPDATE, DELETE, or MERGE operations), then it could be that you’ve accidentally used the wrong prefix for the affected column/s.

Continue reading

A Possible Cause for the 102 Error When Running a Subquery in SQL Server

If you’re running a subquery in SQL Server, but you’re getting error 102 that reads something like “Incorrect syntax near ‘;’“, there could be any number of reasons, because this is a generic error that simply means wrong syntax.

But one possible cause could be that you haven’t declared an alias for the subquery.

Continue reading

Possible Reason You’re Getting an Error When Using HANDLER … LAST or PREV in MySQL

If you’re getting an error when specifying LAST or PREV for MySQL’s HANDLER statement, it could be that you’re trying to do a table scan using the index syntax.

While the HANDLER statement does accept the LAST and PREV options, we can only use them with an index.

So to fix this issue, be sure to specify an index when using the LAST and PREV options.

Continue reading

Possible Reason You’re Getting an Error When using a Qualified Table Name with MySQL’s HANDLER Statement

If you’re getting MySQL error 1064 which reads something like “1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘READ FIRST’ at line 1” when using the HANDLER statement in MySQL, it could be that you’re qualifying the table with the database name when trying to read from or close a table.

Continue reading

Fix “Ambiguous column name” in SQL Server (Error 209)

If you’re getting an error that reads “Ambiguous column name“, followed by a column name, in SQL Server, it could be that you’re performing a join on two tables, but you’re not using the table names or aliases when referring to columns within those tables.

To fix this issue, be sure to qualify the columns with their table names or aliases.

Continue reading

Fix “An aggregate may not appear in the OUTPUT clause” in SQL Server (Error 158)

If you’re getting an error that reads “An aggregate may not appear in the OUTPUT clause” in SQL Server, it appears that you’re using an aggregate function in the OUTPUT clause.

Aggregate functions are not allowed in the OUTPUT clause.

To fix this issue, remove any aggregate functions from the OUTPUT clause and try applying them to a table or table variable.

Continue reading

Fix “Cannot insert the value NULL into column” in SQL Server (Error 515)

If you’re getting an error that reads something like “Cannot insert the value NULL into column ‘CatId’, table ‘demo.dbo.Cats’; column does not allow nulls. INSERT fails” in SQL Server, it’s because you’re trying to insert a NULL value into non-nullable column (i.e. a column with a NOT NULL constraint).

We can’t insert NULL values into non-nullable columns.

To fix this issue, insert a non-NULL value.

Continue reading