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.
Category: DBMS
Database Management Systems
Subtract Days from a Date in PostgreSQL
We can subtract one or more days from a date in PostgreSQL with the - operator.
Fix “ERROR 1136 (21S01): Column count doesn’t match value count at row 1” when Inserting Data in MySQL
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.
Continue reading11 Ways to Find Duplicate Rows while Ignoring the Primary Key in SQLite
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.
Continue readingFix Error “ORA-01789: query block has incorrect number of result columns”
If you’re getting the error “ORA-01789: query block has incorrect number of result columns” in Oracle Database, then it’s probably because you’re trying to use an operator such as UNION, INTERSECT, or EXCEPT to run a compound query, but the SELECT statements on either side of the operator return a different number of columns.
To fix this, simply ensure that both queries return the same number of columns.
Continue readingDetect Whether a Value Contains at Least One Numerical Digit in Oracle
The following example returns all rows that contain at least one numerical digit in Oracle Database.
Continue readingFix “ERROR 1250 (42000): Table ‘…’ from one of the SELECTs cannot be used in ORDER clause” in MariaDB
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.
Continue readingExample of a Simple Merge Statement in SQL Server
The following example uses T-SQL to merge two tables in SQL Server. It uses the VALUES table value constructor as the source table.
PostgreSQL DATEADD() Equivalent
Updated 20 April 2024 to include the date_add() function.
SQL Server has its DATEADD() function that adds an interval to a date value. MySQL’s DATE_ADD() and ADDDATE() for does the same thing, as does MariaDB’s DATE_ADD() and ADDDATE(). SQLite has a DATE() function that also provides the option of adding an interval to a given date.
Prior to version 16, PostgreSQL didn’t have a DATEADD() or equivalent function. But with PostgreSQL 16 came with the introduction of the date_add() function, which allows us to add an interval to a timestamp with time zone.
We can also add and subtract values from dates with date/time operators such as + and -.
3 Ways to Convert an Integer to Decimal in SQL Server
Here are three options for converting an integer to a decimal value in SQL Server using T-SQL.
Continue reading