We have a long standing problem with a certain deadlock in our DB. The conflicting queries are almost always these two: UPDATE db.tbl SET pid = 68111 WHERE pid IS NULL AND processed IS NULL AND `time` <= now() LIMIT…
The dataset is like this: g1 = {d1,d3,d5} g2 = {d1,d3,d2} g3 = {d4,d3,d5} I have the following table called director_genre: director_id | genre d1 g1 d1 g2 d2 g2 d3 g1 d3 g2 d3 g3 d4 g3 d5 g1…
I need to fetch records of last date of each month based on the dates of records in the database table. Now, if I fetch the last dates of each month in my table using following query: SELECT MAX(`date`) AS…
I have a MySQL database which holds one column years_valid_for as bigint and another column completion_date as datetime(6). I have to subtract bigint columns number as years. e.g. years_valid_for is 4 and completion_date is 2023-06-07. I have to subtract 4…
I have 2 tables in mysql database as followed: character_classes table: +--------+----------+ | id | name | +--------+----------+ | CLA001 | assassin | | CLA002 | knight | | CLA003 | vanguard | +--------+----------+ player_inventories table: +--------------+----------------------+ | player_id |…
I am trying to find out unique rows from the given data In my case I am storing buy sell of the stock in stock table. So same client can buy sell same stock. So ask is to just fetch…
I have a PHP script to save new articles into MySQL with the following parameters: ID, Date, Category, Title, Content, Slug Where the Slug is the same title but with - dashes like this: this-is-a-nice-title Then I have the articles.php…
I have a dependency Table Sellars which is having the City ID only. But while showing the sellars I need to show the Sellar country, state. I have written the join query it is returning rest of the sellar details…
DATE ACCOUNT VALUE 2023-06-06 1234 100 2023-06-07 1234 120 2023-06-08 1234 80 2023-06-06 3456 40 2023-06-07 3456 60 2023-06-08 3456 80 2023-06-05 5648 600 2023-06-06 5648 800 2023-06-06 5648 650 2023-06-07 5648 0 2023-06-08 5648 0 I'm passing current date…
For example, there are two tables like this: 1.original table date_str 2016-02 2016-02-02 2.new table(Created based on the original table, an additional is date field is added) date_str is_date 2016-02 2016-02-02 date_str in the original table should be in date…