Do I need a CURSOR for this? – Mysql
I am trying to step through a table and update an attribute based on the below query. When I run the below, it set's all values to 52, so it is recognising the first case and applying to all values.…
org.springframework.beans.factory.UnsatisfiedDependencyException Error creating bean (repository) – Mysql
I'm trying to set up a Spring Boot/MySQL/docker project. After adding the repository classes, I started getting this error: Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'filterController': Unsatisfied dependency expressed through field 'filterService': Error creating bean with name 'filterServiceImpl':…
How to partition a table by year and then subpartition by month in mysql 8
I have a table that contains a month and a year column. I have a query which usually looks something like WHERE month=1 AND year=2022 Given how large this table is i would like to make it more efficient using…
Performance drop upgrading from MySQL 5.7.33 to 8.0.31 – why did it stop using the Index Condition Pushdown Optimization?
I have a table like this (details elided for readability): CREATE TABLE UserData ( id bigint NOT NULL AUTO_INCREMENT, userId bigint NOT NULL DEFAULT '0', ... c6 int NOT NULL DEFAULT '0', ... hidden int NOT NULL DEFAULT '0', ...…
MySQL Upsert with Insert Only Column Value
In MySQL, is it possible to do an upsert but only set a column value on insert (and not set the column value on update). For example, for a createdBy column, we only want to set the value on insert,…
Update a column value if two specific conditions are met in MySQL
I'm very much a newbie to programming. I am trying to update my table column labelled 'bonus' with a new value of 505.99 if two conditions are met: if the givenname is Mary and their bonus is currently 155.99, or…
How do I create a SELECT conditional in MySQL where the conditional is the character length of the LIKE match?
I am working on a search function, where the matches are weighted based on certain conditions. One of the conditions I want to add weight to is matches where the character length of the query string in a LIKE match…
Update duplicate records with incremental column – Mysql
-- create CREATE TABLE employee ( emp_id INTEGER, job_code TEXT, cnt_check int ); -- insert INSERT INTO employee(emp_id,job_code) VALUES (0001, 'JC001'); INSERT INTO employee(emp_id,job_code) VALUES (0001, 'JC001'); INSERT INTO employee(emp_id,job_code) VALUES (0002, 'JC002'); INSERT INTO employee(emp_id,job_code) VALUES (0002, 'JC002'); INSERT…
How does select query works – Mysql
I need to return value in preference order, if the value starts with a it should be returned first, if it does not exist we return the value which starts with b. SELECT value FROM table one WHERE value LIKE…
How to insert date in a new table in YYYY-MM-DD' format? – Mysql
CREATE TABLE ORDERS ( ORD_NUM NUMERIC(6,0) NOT NULL PRIMARY KEY, ORD_AMOUNT NUMERIC(12,2) NOT NULL, ORD_DATE DATE NOT NULL, INSERT INTO ORDERS VALUES('200100', '1000.00', '08/01/2008'); INSERT INTO ORDERS VALUES('200110', '3000.00', '04/15/2008'); INSERT INTO ORDERS VALUES('200107', '4500.00', '08/30/2008'); Since I have large…