Relational – Page 114

Fix “ERROR 1054 (42S22): Unknown column ‘…’ in ‘order clause'” when using UNION in MySQL

September 17, 2023February 28, 2022 by Ian

If you’re getting error “1054 (42S22): Unknown column ‘…’ in ‘order clause'” when using the UNION clause in MySQL, it could be because you’re trying to reference an aliased column by its column name.

Fix “ERROR 1136 (21S01): Column count doesn’t match value count at row 1” when Inserting Data in MySQL

February 27, 2022 by Ian

One of the more common error message in MySQL goes like this: “ERROR 1136 (21S01): Column count doesn’t match value count at row 1“.

This error typically occurs when you’re trying to insert data into a table, but the number of columns that you’re trying to insert don’t match the number of columns in the table.

In other words, you’re either trying to insert too many columns, or not enough columns.

11 Ways to Find Duplicate Rows while Ignoring the Primary Key in SQLite

July 23, 2023February 26, 2022 by Ian

Here are eleven options for returning duplicate rows in SQLite when those rows have a primary key or some other unique identifier column (but you want to ignore the primary key).

This means that the duplicate rows share exactly the same values across all columns with the exception of their primary key/unique ID column.

Fix “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in ORDER clause” in MariaDB

September 17, 2023February 25, 2022 by Ian

If you’re getting “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in ORDER clause”, it’s probably because you’re qualifying a column name with its table name when using an operator such as UNION, INTERSECT, or EXCEPT in MariaDB.

To fix this, either remove the table name or use a column alias.

Example of a Simple Merge Statement in SQL Server

February 24, 2022 by Ian

The following example uses T-SQL to merge two tables in SQL Server. It uses the VALUES table value constructor as the source table.

3 Ways to Convert an Integer to Decimal in SQL Server

February 23, 2022 by Ian

Here are three options for converting an integer to a decimal value in SQL Server using T-SQL.

SQL Server SHOW TABLES Equivalent

February 22, 2022 by Ian

Every now and then I find myself typing SHOW TABLES in SQL Server, expecting to get a list of tables.

That would make perfect sense if I was using MySQL or MariaDB. But SQL Server/T-SQL doesn’t have a SHOW TABLES statement like MySQL or MariaDB, so it never works. And I keep forgetting. But fortunately, SQL Server does have alternatives.

Here are five options for getting a list of tables in SQL Server. These can be used whenever you’re trying to find that elusive SHOW TABLES statement in SQL Server.

SQLite INTERSECT Operator

February 22, 2022 by Ian

In SQLite, the INTERSECT operator is used to create a compound SELECT statement that returns the intersection of the results of the left and right SELECT statements. In other words, it combines two queries, but returns only those rows that are returned in both queries.

SQLite EXCEPT Operator

February 21, 2022 by Ian

In SQLite, the EXCEPT operator can be used to create a compound SELECT statement that returns the subset of rows returned by the left SELECT that are not returned by the right SELECT.

Fix Msg 512 “Subquery returned more than 1 value” in SQL Server

September 17, 2024February 21, 2022 by Ian

If you get error Msg 512 that reads “Subquery returned more than 1 value…” in SQL Server, it’s because you’re using a subquery that returns more than one value in a scenario where this is not allowed.