Skip to content

Tag: mariadb

SQL query that returns the primary key if found, NULL otherwise

Given a table like I am looking for a query that searches for a given name, returns the id if found, and NULL otherwise. Sort of what left join can do, except here there is no left table. Answer Use aggregation with MAX() (or MIN()): An aggregation query like this always returns exactly 1 row with 1 column an…

Get data from same row with MAX()?

[MariaDB 10.4, PHP8.0] How can I rewrite this so it works so it get weight_date from the row with MAX(total_weight) and list it AS highest_weight_date? I have read that we can’t use MAX() among with WHERE? I have tested to rewrite several examples, but I give up and embarrassed show my latest try: I try…

Order of CAST() and COALESCE() matters in MariaDB

I have a strange problem: There is a price in a JSON column in a table and the following statements give different results while they should give the same thing: Just to check I also added a JSON_EXTRACT(item.price_details, “$.shipping.price”) AS shipprice Result: MariaDB version: mariadb Ver 15.1…

MariaDB Case statement

I have the following SQL-Code in a Mariadb-Database: (1) I need the following result: If Matrix=’AW’ => I need the field “FaktorAW” else => I need the field “FaktorGW” is it possible to formulate Statement (1) with a “case statement”? Answer Of course, thi…

Can’t remove duplicates from MariaDB 10

I’m having hard time removing duplicates from database. It’s MariaDB (protocol version: 10, 10.3.34-MariaDB Server). I need to remove rows where three columns are equal. I was trying to use WITH clause but database throws error that it can’t recognize ‘WITH’, so I focused on trad…

Why concatenation does not work in Dbeaver?

Using Dbeaver, the || concatenation does not work. Had to use CONCAT() instead. Got output error message: Truncated incorrect DOUBLE value. Anyone else had this issue? Skill level: Beginner Editor: Dbeaver OS: Windows 7 Answer MariaDB expects the PIPES_AS_CONCAT to be set with the sql_mode to use || as a conc…

Cannot create trigger if-delete-clause

Here is the code I have which checks if there is more than 5000 entries after an insert, then deletes all entries which are greater than 2 hours old I am getting you have an error in your mysql syntax near IF (SELECT … DELETE FROM … I am using MariaDB, can someone help me understand where is the e…