114 questions
-1
votes
1
answer
630
views
Need variables in parameterized view in clikhouse
I need variable in parametrized view.
CREATE VIEW my_new_view AS
SELECT
CampaignName,
sentiment, -- **CASE WHEN variable** = sentiment THEN sentiment ELSE NULL END AS sentiment_match
COUNT(CASE ...
0
votes
0
answers
41
views
using a variable in a SUBQUERY is returning more than one row
I'm trying to reuse the @FleetNumber variable to get the Truck's Trailer registration Number through the fleet number <fleetnumber>T eg.393T. The Trailer has the fleet number of the <...
0
votes
0
answers
13
views
MySQL - partition table by key field and select latest row ordered by event column as view [duplicate]
I am running MySQL Community Server v5.7.40 and have 2 tables: "customers" and "events" created as follows:
create table customers (customer_id int, name varchar(100), primary key (...
- 674
-1
votes
1
answer
37
views
I want the highest and the lowest value of a table, why can't I save that value in PHP?
$category = htmlspecialchars($_GET['category']);
$sql = "(SELECT number
FROM german
WHERE german.category_german LIKE ".$category."
ORDER BY number DESC
...
- 31
0
votes
1
answer
61
views
Mysql select query with ordering issue
I want to display one paid status(1) column and then 3 unpaid(0) status column respectively.
Paid status is taken based on Expirydate, the date greater than now is considered paid status and date ...
1
vote
0
answers
152
views
Ansible module mysql_variables not fetching WSREP variables
I am using below block to fetch mysql variables.
- name: Check for wsrep_cluster_status setting
mysql_variables:
login_user: '{{ mysql_root_user }}'
login_password: '{{ mysql_root_pwd }}'
...
-1
votes
2
answers
602
views
How to store a result of a row in a variable and use it in the next row in MySQL SELECT query? [duplicate]
I have the following tables:
Stocks
id
name
amount
1
Pen
35
2
Cd
21
3
Bag
15
StockUnits
id
name
include
stockid
1
onepen
1
1
2
dozen
6
1
3
pocket
24
1
4
onecd
1
2
5
pocket
5
2
6
onebag
1
3
So how do I ...
- 588
0
votes
1
answer
507
views
Use the result of a MySQL query, as a variable, with the IN-operator
Looking for a way to return a variable that can be used inside a IN-operator.
My current result returns: 1,2,3,
but I guess the variable should be more like this: '1','2','3' to be able to use it.
...
- 963
-1
votes
1
answer
974
views
why do i get error Must declare the scalar variable "@x". while the query works in the sql window?
the error that I'm getting is " Must declare the scalar variable "@x"."
I'm trying to declare a variable @x and make him a NVARCHAR(50)
then id like to subtract an amount from it ...
- 21
1
vote
1
answer
898
views
What is the other way to get result without using MYSQL ROW_NUMBER Function with PARTITION and multiple group by to reset row count
select
Id,request_id,key_skill_id,
ROW_NUMBER() OVER (PARTITION BY skill_id
ORDER BY request_id,skill_id) sequence
from report
where id= x
GROUP by request_id, ...
0
votes
1
answer
2k
views
MYSQL Strict Mode
For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use ...
- 115
2
votes
1
answer
166
views
MySQL Select statement - iterations on one row
The goal is to variate price over a large range of numbers to be able to plot a figure. My current query returns one row with name and a default price.
I tried adding an increment variable:
select
...
- 21
1
vote
2
answers
82
views
i get an uppercased table name calling a variable
I set a variable name for a table as lowercase:
Set TGT_TABLE = 'tab_name';
however when i try to call it:
select * from table($TGT_TABLE);
I get an error:
SQL compilation error: Object 'TAB_NAME' ...
- 519
0
votes
1
answer
190
views
In MySQL, how can you replace variables by using WITH?
I have a query for comparing statement balances with expense records.
I would like to make it a View, but it is using two variables, which you can't use in a View.
It occurs to me that it should be ...
- 343
0
votes
0
answers
39
views
How to use MySQL variables in PHP? [duplicate]
I am trying to do something like this:
SET @grade_id = (SELECT grade_id FROM grades_table WHERE grades = '1' AND sections = 'A');
SET @subject_id = (SELECT subject_id FROM subjects_table WHERE ...
0
votes
3
answers
3k
views
Deleting records in MySQL WHERE id IN (@VARIABLE) -- (2,3,4)
Is there is a way to delete records using WHERE IN @VARIABLE?
-- DEMO TABLE
CREATE TABLE people (
id int AUTO_INCREMENT NOT NULL,
name varchar(100),
age int,
active smallint DEFAULT 0,...
- 19k
0
votes
0
answers
160
views
Using a mysql variable as parameter for AGAINST
I am fetching data from an excelsheet using LOAD DATA INFILE statement and trying to evaluate the results before saving to a database. I need to be able to pass one of the fields from the excel as a ...
- 72
1
vote
1
answer
170
views
How to call a column dynamically in the WHERE clause in SQL
I'm trying to figure out how to call the column in the table where the column name matches the value of a declared variable. In this example, Tbl_B has columns labeled as DPS_YYYYMM such as '...
- 143
0
votes
1
answer
114
views
Using the input parameters from stored parameters as variables inside where clause MySQL
I have a simple mysql table:
CREATE TABLE `cont` (
`ID` int(11) NOT NULL,
`Meeting_id` int(11) DEFAULT NULL,
`member_name` varchar(20) NOT NULL,
`cont_prod` varchar(20) NOT NULL,
`...
1
vote
1
answer
247
views
MySQL / MariaDB place COUNT(*) in equation
I'm trying to get the number of rows in a table or column and place that value inside an equation, like this:
UPDATE myTable
SET myCalculatedColumn = COUNT(*) / (@rownum:= 1 + @rownum)
WHERE 0 = (@...
- 1,313
0
votes
1
answer
827
views
How to store entire column values of the query result in MYSQL?
I have a statement,
select customer_id,count(customer_id), sum(total_price), date_time
from the_order group by customer_id;
The Result,
cus1 15 437700 2021-04-23
cus2 1 12000 2008-5-9
...
- 912
0
votes
1
answer
200
views
Run different SQL statements using If Then with a variable
Trying to do something very simple. Using If/Then, is there a way to run a separate Select statement based on the value of a variable?
The function GetTotalActiveUnits() in the below code returns an ...
- 27
0
votes
1
answer
43
views
How can this MySQL query return NULL?
SET @last_nid=-1;
SELECT vid,
IF(@last_nid = nid, @last_status, 0) AS last_status,
@last_status := status AS status,
@last_nid := nid AS nid
FROM node_field_revision
ORDER BY vid;
...
- 11.9k
-1
votes
2
answers
1k
views
Catch stored procedure output into a variable without using out params
Consider the following procedure:
CREATE PROCEDURE `getFoo`()
BEGIN
select 'bar' as foo;
END;
Once call-ed, it outputs:
{"foo":"bar"}
But what if getFoo is invoked within a ...
- 915
2
votes
1
answer
8k
views
How to SET have_query_cache = YES?
I've tried to set this by using SET VARIABLE but it said that have_query_cache is read-only
mysql>SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+-...
- 31
4
votes
1
answer
5k
views
Am I calculating maximum connection limit on our MySQL db correctly?
I have a MySQL database in production and I am trying to calculate the optimised number to set the MySQL max_connections value to - Am I doing this correctly as my sums seem quite high to me.
As far ...
- 14.2k
0
votes
1
answer
304
views
Declare a variable based on name in sys.tables then delete the table based on that variable in dynamic SQL
So what I expect is for the first piece of code to find the table name then if that table name exists and is more than 3 days old drop that table.
My issue with this code is that the code is not ...
- 408
1
vote
1
answer
1k
views
What is the use of MYSQL8 variable 'character_set_database'?
I use the SQL 'show variables like 'character%';' to show the character set in MySQL variables.I find a variable named 'character_set_database', I want to know the use of it. I get some info from the ...
- 83
1
vote
1
answer
171
views
Error on "User Defined Variable" assigned as dynamic Field Alias
I have created MySQL query interpolation below in Workbench. The query was so fit to my needs except that I could not effect the "User Defined Variable" @var1/2/3 as my field aliases.
I am ...
- 639
0
votes
1
answer
441
views
Must declare scalar variable in a unit test
I am trying to design a unit test that ensures that a message is bigger than a certain number of characters. My code is:
EXEC tSQLt.NewTestClass 'TestMarketingEnoughRows';
GO
CREATE OR ALTER ...
- 59
0
votes
1
answer
38
views
how to transform results of syntax to become variable mysql
i have a table with order_buyer_id as the id of transaction, createdby as the id of the buyer, createdAt as the date which transaction happen, and quantity as the weight of each transaction.
on my ...
- 572
0
votes
1
answer
515
views
mySQL updating row with a procedure using a dynamic column name
I have been trying to write a stored procedure that involves a user entering in the column they wish to store data in and then the data to be stored. So far this works but the column is hard coded.
...
- 23
0
votes
2
answers
2k
views
How to declare a variable within the same statement in MySQL?
How do I create variables in MySQL that I can refer to in the same statement?
Does this work for UPDATE and INSERT statements as well?
I asked this because I got a lot of SQL text files where I ...
- 585
0
votes
1
answer
594
views
Is there a way for Ansible mysql_variables module to set variable persistent?
I have an Ansible playbook with this task..
---
- name: Set mysql_variable
mysql_variables:
variable: mysql_variable
value: 2000
- name: Reload MySQL
service:
name: mysql
state: ...
- 441
0
votes
1
answer
937
views
MySQL: How to use main query variable inside sub query WHERE condition
Here is my Table "assets"
+----+----------------------------+-------+-----------+
| id | model | grade | warehouse |
+----+----------------------------+-------+-----------+
| 1 |...
- 5,359
0
votes
1
answer
41
views
MySql use variable in WHERE
When I write code:
SET @code = 'a123';
UPDATE my_table SET number = (number + 1) WHERE code = @code;
it doesn't work correctly.
But if I write code:
UPDATE my_table SET number = (number + 1) WHERE ...
- 359
1
vote
3
answers
111
views
How to fetch rows from which sum of a single integer/float column sums upto a certain value
I have a table. It has the following structure
goods_receiving_items
id
item_id
quantity
created_at
I am trying to fetch rows against which have the following conditions
Has one item_id
When the sum ...
0
votes
2
answers
169
views
SQL variable assignment and comparison logic
I am looking into the following mysql:
select num, @record,
case
when @record = num then @count:=@count+1
when @record <> @record:=num then @count:=1 end as n
from
Logs ,(select @...
- 25.6k
0
votes
1
answer
32
views
What's the difference two tables side by side and join tables?
I am looking into the following sql codes, trying to understand but confused:
What does the query actually do when we put the two tables together? What's the difference between this and joining the ...
- 25.6k
1
vote
0
answers
151
views
Mysql row-based binary logging behaving different in some versions of mysql (rows grouping)
Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql.
Let's assume the following statement update three rows:
UPDATE table_name ...
- 1,351
0
votes
1
answer
558
views
How to put variables in a SQL Server comment?
Sadly, we do auditing through inline comments in SQL queries, like this:
begin transaction;
EXEC someStoredProcedure; --this was executed by Bob
commit transaction;
I'm trying to automate it by ...
- 1,983
-1
votes
1
answer
29
views
MySQL running total only working with hard coded integer
I have the following SQL which is not working how I thought it would.
SET @runtot:=0;
select
q1.week,
(@runtot := @runtot + cast(q1.flying_hours as SIGNED)) as flying_hours,
q1.flying_hours as ...
- 5,527
3
votes
1
answer
1k
views
MySQL Illegal mix of collations when using user-defined variables inside LOCATE
I have a query like below to remove from a table column certain substrings which begin and end with particular substrings:
UPDATE om_posts SET post_content=REPLACE(post_content, SUBSTRING(
...
- 1,022
6
votes
6
answers
3k
views
Show the default value for a variable
I am using SET GLOBAL <variable> = <value> to modify dynamic settings in mysql and I am wondering if there is some way to get the default value for each variable? For instance, if I use ...
- 30.4k
0
votes
1
answer
198
views
List all variables that can be modified during runtime
Is there any way to get all system variables that can be modified during runtime? I have tested using SHOW VARIABLES but this returns all variables and not only the one that can be changed during ...
- 30.4k
1
vote
2
answers
2k
views
Mysql: "SET @Variable = 0" in one query [Java]
I have two sql queries. But in Java I can't set variables.
I tried to summarize it to one query. But that not works, because the sql syntax is wrong.
SET @rn = 0;
SELECT *
FROM (SELECT t.id, @rn :=...
- 21
0
votes
1
answer
542
views
Mysql : How to add data to the existing data stored in a variable?
I wish to add data to the existing variable which I created in Mysql.
set @variable = select * from b where b.id = 35;
@variable = select * from b where b.name = "dheeraj";
How can I go ahead with ...
- 739
0
votes
1
answer
1k
views
Set binlog for Mysql 5.6 & Ubuntu16.4
in /etc/mysql/my.cnf:
[mysqld]
log_bin=mysql-bin
binlog_format=ROW
server-id=11
then on mysql client:
mysql> show variables like '%binlog%';
+-----------------------------------------+------------...
- 317
-1
votes
3
answers
58
views
Having trouble with mysql variables, what am I doing wrong here?
See below and test here.
I've been pulling my hair out over this for a couple of hours now. I've searched many posts and as best I can tell everything is correct.
I'm having trouble with the IF ...
- 3,302
0
votes
1
answer
31
views
Query build automation
I have the following query
select count(t1.guid)
from table t1
where t1.id=X;
X is a result-set from this query
select ID
from table t2
where t2.flags=65537;
The above query returns 84 results, ...
- 101