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 and if
Tag: mariadb
How to update only one of duplicated rows in SQL without primary keys?
I have a table that have the following columns: st_id sbj_id desc scr sbm_dt 2001 10 Q2 7.4 2021-05-03 17:03:32 2001 10 Q1 8.0 2021-04-03 18:07:35 2011 10 Q1 5.0 2021-04-03 19:07:35 2001 10 Q2 7.4 2021-05-03 17:03:32 I want to update the st_id value on the last row of the table to 2011. How can I update only one
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 to add it to this
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 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 DB Fiddle (I couldn’t use
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, this is possible. Basically you can do this: You have to take care if this is really
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 traditional way. I need to remove rows where foreignId, column1 and column2 are equal. I’m
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 concat operator. One can check what is set for sql_mode
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 error
Update a a column in some specific items of a table, with values from a column in another table
I have 3 tables. I want to update all Image rows from table 1, with local image from table 3. Table 1 is liked with table 2 by ID. Table 2 and 3 are linked by itemRef. As an example of what I want: is that ID 1 from table 1, gets image A. Because ID from table 1, is
How do I find the number of video games with more than 5 developers. (SQL query)
Lets say i have 2 tables which are videogames and developers. Under the videogames table, we have the following attributes: videogameid(PK) title dateOfRelease Under the developer table, we have attributes: developerid(PK) name position I tried finding the total number of videogames that had more than 5 developers. Here is my attempt to resolve the issue. While I did not get