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 readingThe following example uses T-SQL to merge two tables in SQL Server. It uses the VALUES table value constructor as the source table.
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 -.
Here are three options for converting an integer to a decimal value in SQL Server using T-SQL.
Continue readingEvery 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.
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.
Continue readingIf you ever encounter a character column that should be a numeric column, there’s always a possibility that it contains non-numeric data that you don’t know about.
In Oracle Database, you can run a query like the following to return non-numeric data from the column.
Continue readingHere are seven ways to return duplicate rows in MySQL when those rows have a primary key or other unique identifier column.
Continue readingIf you’re getting error “ERROR 1054 (42S22): Unknown column ‘colname’ in ‘order clause’” in MariaDB, it may be that you’re trying to reference an aliased column by its column name.
This is a common error when running queries that join two or more tables. It can also happen when using operators such as UNION, INTERSECT, and EXCEPT.
Below are two methods for returning rows that only contain alphanumeric characters in MariaDB.
Alphanumeric characters are alphabetic characters and numeric characters.
Continue reading