I am trying to write a query where I select records by some condition, update one column of those records and then return them, all in one transaction. I have tried two options. First with CTE: BEGIN; WITH unwatched_rows AS…
sorry if this is so obviously easy but I am new to this! I am trying to add another WITH statement to a query that works perfectly fine, but breaks when I edit it. The query I am trying is:…
I'm using Laravel package staudenmeir/laravel-cte but am not able to articulate the query I want MY SQL query : WITH T AS ( SELECT Id, DeviceType, DeviceId, ENERGY_Total, `Time` FROM devices_sensor_data WHERE `Time` BETWEEN '2023-03-28 00:00:00' AND '2023-03-27 20:40:00' AND…
I am trying to find a way to delete all rows with NULL values in a table. On this post, I saw that you can't specify a target table from updates in the FROM clause. Therefore, I tried building a…
I built a dynamic query that returns Total count of entries in CTE Paginated entries from CTE List of column names used in CTE Requested page size and page number The beauty of in returning total count + pagination in…
I have created my first MySQL query to get the minimum LTP by MarketName/SelectionName. It works ok but I'm struggling to work out how to get the rest of the fields from the original table (checktable). I just want the…
I have two tables with many to many relationship in Postresql database. The first Topic table consists of three columns. Their names are id, name and parent. Topic table has hierarchial structure: id name parent 1 Mathematics 0 2 Algebra…
I am curious what techniques Database Developers and Architects use to create dynamic filter data response Stored Procedures (or Functions) for large-scale databases. For example, let's take a database with millions of people in it, and we want to provide…
I'm having great difficulty writing this query and cannot find any answers online which could be applied to my problem. I have a couple of tables which looks similar to the below with. Each purchase date corresponds with an item…
I am using this code to see the unique results of concated/joined columns 'a,b' from table1 and is working perfectly WITH tt as ( SELECT concat(a,',',b) AS concated FROM table1 GROUP BY concated ) select concated from tt; But, instead…