If you’re getting MySQL error 1066 which reads something like “1066 (42000): Not unique table/alias: ‘products’” when using the HANDLER statement in MySQL, it could be that you’re trying to open a table that’s already open.
If this is the case, be sure to close the table before trying to open it again. Or simply continue working without opening the table again.
MySQL provides us with the ability to create server-side prepared statements, which are precompiled SQL queries that can be executed multiple times with different arguments.
We can use the performance schema to return a list of all prepared statements in the server, along with useful information about each prepared statement.
If you’re getting an error that reads something like “1052 (23000): Column ‘name’ in field list is ambiguous” in MySQL, it looks like you could be referencing a column name in a query without qualifying it with the table name.
This can happen when you perform a join between tables that use the same name for one or more columns.
To fix this issue, be sure to qualify column names with the table names when performing joins across tables.
Any time you prepare to delete data from a database, you will probably wonder whether or not to log the deleted data somewhere… just in case.
One way to do this is to construct a SELECT statement to identify the rows you want to delete, and then use it to copy those rows into a separate log table, before doing the actual delete. If you’re using SQL Server, another option is to use the OUTPUT clause. This article discusses the later.
MySQL provides us with the ability to create multiple prepared statements and run them as many times as we like, while changing the parameter values with each run.
While prepared statements are only available to the session that created them, they are stored in the server. So it’s quite possible that a server could have a build up of lots of prepared statements hanging around once different users have created them.
There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key’s index.
For example, we may want to identify the index’s name if we need to disable the primary key for some reason (we disable primary keys by disabling their index).
The query below can help if you need to find the name of a primary key’s index for a given table.
If you’re getting an error that reads “Syntax ‘*’ is not allowed in schema-bound objects” in SQL Server, it appears that you’re using the asterisk wildcard (*) to select all columns when trying to define a schema-bound object (like a schema-bound view).
As the error message states, this is not allowed.
To fix this issue, explicitly name each column.
If you’re getting an error that reads something like “ERROR 1066 (42000): Not unique table/alias: ‘d’” in MySQL, it could be that you’re trying to assign a duplicate alias to a table. Or it could be that you’re doing a self-join without assigning table aliases.
Table names and aliases must be unique when doing queries in MySQL.
The error can also happen if you use HANDLER to open a table, but then try to open it again before closing it.
To fix this issue, be sure to use unique table aliases in your query. And if you’re using HANDLER, either close the table or continue working with it (without trying to open it again).
There are several ways that we can list all indexes in PostgreSQL. These include utilizing various system catalog views and psql commands. Below are five options for returning a list of indexes in PostgreSQL, along with examples of how to filter the results by index name, table name, and other criteria.
Sometimes when working with SQL Server, we need to get a quick list of indexes on a given table.
Here are three queries that return all indexes on a given table in SQL Server.