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.
SQL Server OUTPUT Clause: Using Expressions to Analyze Changes to the Data
When we use the OUTPUT clause in SQL Server, we have the option of providing expressions instead of just the column names. For example, we could use an expression that compares the old price with the new price and returns the difference. Such data could be handy, depending on what your goals are.
Let’s dive straight into an example that uses an expression in the OUTPUT clause.
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 readingWhat is a UNIQUE Key in SQL?
In SQL, a UNIQUE key is a column or set of columns that can uniquely identify a row in a table. These are also candidate keys. Only one candidate key can become the primary key for a table. All other candidate keys can then be referred to as UNIQUE keys, given they can uniquely identify a row in a table.
Using INSERT … ON DUPLICATE KEY UPDATE in MySQL
If you need to do some inserts and updates in MySQL, one option is to run separate INSERT and UPDATE statements. Another option is to use the INSERT ... ON DUPLICATE KEY UPDATE statement. Depending on the scenario, this can be a handy option.
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.
Using the INCLUDE Option on an Index in SQL Server
The INCLUDE option in SQL Server allows us to include non-key columns in a nonclustered index. These columns are not part of the index key (which SQL Server uses to order and search the index), but they are stored with the index pages.
The INCLUDE option can significantly improve query performance when additional columns are needed by a query but are not part of the index key.
Example of PostgreSQL Automatically Creating a Nested Composite Type
Whenever we create a table in PostgreSQL, a composite type is automatically created behind the scenes. This composite type is based on the table that we created. Each column in the table becomes a field in the composite type.
If the table already uses a composite type, then the composite type that PostgreSQL creates will include that type in its definition, thereby creating a situation where we effectively have a nested composite type.
Continue readingFix “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 readingUsing INSERT INTO with a CTE in SQL Server
Common Table Expressions (CTEs) are a useful way to simplify complex queries in SQL Server (and other SQL databases). More often than not, we’ll see CTEs that are used by SELECT statements, but this isn’t necessarily required. CTEs can be followed by SELECT, INSERT, UPDATE, DELETE, or MERGE statements.
This article provides a simple example of using INSERT INTO with a CTE in SQL Server.