Here are examples of removing duplicate rows from a table in MariaDB when those rows have a primary key or unique identifier column.
The examples delete duplicate rows but keep one. So in the case of two identical rows, it deletes one of them and keeps the other.
Continue readingIf you’re getting “ERROR 1222 (21000): The used SELECT statements have a different number of columns” when using the UNION clause in a MySQL query, it’s probably because the number of columns returned by each SELECT statement is different.
SQLite’s DATE() function provides us with the ability to return the date of the first instance of a given day of a given month. Therefore, we can use it to return the first Monday of a given month. We can also use it to return the first Tuesday, Wednesday, Thursday, Friday, etc.
We can use DATETIME() if we want a datetime value to be returned.
If you have a Unix timestamp, you can use SQLite’s DATETIME() function with the unixepoch modifier to compute the actual date and time.
You can alternatively use the DATE() function if you only need the date to be returned. And it’s also possible to use the TIME() function to return just the time portion.
In PostgreSQL, we can use the + operator to add one or more months to a date. There are several options when it comes to specifying the actual number of months.
Here are three options for returning rows that contain uppercase characters in MySQL.
Continue readingBelow are four ways to list out the tables in a MariaDB database using SQL or the command line.
Continue readingSQLite doesn’t have a SHOW TABLES statement like MySQL and MariaDB have, but it does have a similar command.
In SQLite, you can use the .tables command to show a list of tables. You can alternatively use the table_list pragma to do the job.
SQLite 3.38.0 was released on 2nd February 2022. With it came some enhancement to the date and time functions.
One of those enhancements is the addition of the UNIXEPOCH() function. This function returns a unix timestamp. That is, the number of seconds since 1970-01-01 00:00:00 UTC.
If you have a table that you suspect has duplicate rows in your MariaDB database, you can use any of the following queries to get an idea of how many rows are duplicates.
Continue reading