Is it possible to use user variables in an update statement, I can find only
scant docs on it, but they do not pertain to what I am trying to do.
Given this select:
SELECT user_id, order_status, quantity_chosen, month_price, each_price,
sales_tax, sales_tax_rate,
@NEW_each_price:=(each_price
Hi Scott,
Scott Haneda wrote:
Is it possible to use user variables in an update statement, I can find only
scant docs on it, but they do not pertain to what I am trying to do.
Given this select:
SELECT user_id, order_status, quantity_chosen, month_price, each_price,
sales_tax, sales_tax_rate
Hi,
I want to set certain _user_ variables, which I will create myself, at
the database startup time so that all clients can use the values
stored [only one time at the beginning] in those variables instead of
computing the same each time in triggers or other code.
It's just like the
Thanks for your kind words of opinion, if you feel you have a better
way please do go ahead , i am going to show you the sql i ended up
using which was a union to append the current summary at the end, i
then had to use php afterwards to add up the totals as i was getting
unexpected results
Dan Rossi [EMAIL PROTECTED] wrote on 12/29/2005 07:19:13 AM:
Thanks for your kind words of opinion, if you feel you have a better
way please do go ahead , i am going to show you the sql i ended up
using which was a union to append the current summary at the end, i
then had to use php
You seem to be coming at SQL with a COBOL perspective. Views are something
you typically create just once and they stay updated automatically. They
work like tables not like queries. Assigning variables to each column of a
view doesn't make any sense (in the SQL sense of view) as each column
Ok i have simplified my query into sections i have discovered that
mysql5 doesnt like user variables in a select statement when creating a
view, i am required to setup user variables so i can send the current
row value primary key to a sub query, i really wished i could just send
the field
Um, thast exactly right each select is a list of results , i want to
merge them then manipulate the data after putting them into a view,
maybe a temp table is needed for this but i dont really want to do an
entire create table statement aswell :\
On 29/12/2005, at 2:48 AM, [EMAIL PROTECTED]
Btwi dont want the column of a view to be a variable, i think thats
what it thinks ! Im just needing to send the value of the current
primary key field top a sub query !
Read my latest post if i can get around not using variables, and still
manage to get the right values of a current row
I just tried to create a Function or Stored Procedure instead of making
variables but it didnt even let me do this
CREATE FUNCTION test (customerID, month, producerID)
RETURN SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
fu.customerID=customerID AND fu.month=month AND fu.feedID IN (SELECT
Dan,
You need to shoot your SQL tutor. Whoever taught you to write aggregate
queries seriously took your money. You DO NOT need to use subqueries to do
what you want to do. You do not need to write a full CREATE TABLE
statement to create a temporary table (see other response). You do not
need
Hi there i am trying to use usewr variables in a select statement to
add to a where clause in a sub query. Ie
select @id:=id,@month:=month, (select SUM(totals) from table where
[EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals from table
its happened on other occasions ie with calculations
Dan Rossi [EMAIL PROTECTED] wrote on 12/27/2005 11:39:57 PM:
Hi there i am trying to use usewr variables in a select statement to
add to a where clause in a sub query. Ie
select @id:=id,@month:=month, (select SUM(totals) from table where
[EMAIL PROTECTED] and [EMAIL PROTECTED]) as totals
I have an unfinished query, i am trying to test, basically im required
to get the value of the current field in a row and use it for a
subquery in that row :| Its not a working query, and im not asking for
someone to fix it, however as u can see i need to send the customerID
and month to the
Hi there I am experiencing a wierd bug in mysql 4.1 which is working in
Mysql5 beta where i am trying to run calculations on user variables. I
use these to pass values to sub queries.
FORMAT((IF(c.bandwidth_limit=0,
((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED],
((@[EMAIL
Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution would be to try it and find out :)
I need to move multiple linked entries (in around 12 tables) from one
running server to another. I'm using auto_increment's all over the place and
I
- Original Message -
From: Neculai Macarie [EMAIL PROTECTED]
To: Mysql mysql@lists.mysql.com
Sent: Thursday, May 12, 2005 1:20 PM
Subject: Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution
Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution would be to try it and find out
:)
I need to move multiple linked entries (in around 12 tables) from one
running server to another. I'm using auto_increment's all over the place
It sounds like you should be doing the link preservation and number
update part in php or perl.
Neculai Macarie wrote:
Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution would be to try it and find out
:)
I need to move
Neculai Macarie [EMAIL PROTECTED] wrote on 05/12/2005 03:26:33 AM:
Not that I'm aware of. What type of conversions are you doing that
you
need 30,000 use vars? An easy solution would be to try it and find
out
:)
I need to move multiple linked entries (in around 12 tables) from
one
.
If you have any questions, I will be lurking ;-)
Thanks for this solution. Indeed it's much simpler then needing 30 000 user
variables :).
--
mack /
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi!
What is the maximum number of user variables that I can have in MySQL ? (I
need to transfer a application from one server to another and I need to
rebuild the links between tables with new id's on the new server (I'm using
auto_increment extensively), and I have a solution that involves many
Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution would be to try it and find out :)
-Eric
Neculai Macarie wrote:
Hi!
What is the maximum number of user variables that I can have in MySQL ? (I
need to transfer a application from one
setting of @iOrder or @mGroup0.
How do the User variables in an UPDATE statement act differently than in
a SELECT statement?
Thanks,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does anyone else have any ideas about this topic?
Thanks
Ed Reed [EMAIL PROTECTED] 04/23/05 1:29 pm
Thanks for the reply,
I realize that user variables disappear when the connection closes but I don't
understand why what I'm trying to accomplish can't be done. Doesn't it make
since
Ed Reed [EMAIL PROTECTED] wrote on 04/25/2005 02:33:23 AM:
Does anyone else have any ideas about this topic?
Thanks
Ed Reed [EMAIL PROTECTED] 04/23/05 1:29 pm
Thanks for the reply,
I realize that user variables disappear when the connection closes
but I don't understand why what
Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection. All my apps
are in VB6 and VBA. They all take a query, open a connection, run query, fill
array from query results, close connection and pass back the array. Because of
backward compatibility
At 22:04 -0700 4/22/05, Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection.
All my apps are in VB6 and VBA. They all take a query, open a
connection, run query, fill array from query results, close
connection and pass back the array. Because
Thanks for the reply,
I realize that user variables disappear when the connection closes but I don't
understand why what I'm trying to accomplish can't be done. Doesn't it make
since that if you can load a single file with multiple SQL commands and have
that work succesfully then you should
I'm having trouble using user variables and I hope someone can help,
My test environment is MySQL 4.1.11 on WindowsXP with MyODBC 3.51.11
If I open a command line client, I can do this
mysql SET @A='Test';
Query OK, 0 rows affected (0.00 sec)
mysql Select
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual that
Thanks for the reply,
So is there anyway to use User Variables with a single connection. All my apps
are in VB6 and VBA. They all take a query, open a connection, run query, fill
array from query results, close connection and pass back the array. Because of
backward compatibility there's
[snip]
This does not work. It appears that the user variable is not picked up
in
the WHERE clause - the query works fine if I have:
[/snip]
You have a fundamental lack of understanding of user variables. A quick
read of http://dev.mysql.com/doc/mysql/en/Variables.html will reveal
User variables
I am having a problem with a query:
SET @SoftwareID = 7;
SELECT
s.softwareID,
s.softwareName,
s.softwareVersion,
s.softwareCreated,
s.softwareChanged,
b.buildName,
s.supportFlag,
s.softwareDesc,
s.softwareLicense,
]
Subject: User variables
I am having a problem with a query:
SET @SoftwareID = 7;
SELECT
s.softwareID,
s.softwareName,
s.softwareVersion,
s.softwareCreated,
s.softwareChanged,
b.buildName,
s.supportFlag,
s.softwareDesc
User variables do not work with REGEXP under MySQL 4.0.21 4.1.5.
Is this a bug or a feature?
--
./ premax
./ [EMAIL PROTECTED]
./ koniec i bomba, a kto czytal ten traba. w.g.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
At 3:12 +0200 10/8/04, Przemyslaw Popielarski wrote:
User variables do not work with REGEXP under MySQL 4.0.21 4.1.5.
Is this a bug or a feature?
It's difficult to provide an answer to this because you're providing
no information about what do not work means. Can you be more specific?
mysql set
Paul DuBois [EMAIL PROTECTED] wrote:
User variables do not work with REGEXP under MySQL 4.0.21 4.1.5.
Is this a bug or a feature?
It's difficult to provide an answer to this because you're providing
no information about what do not work means. Can you be more
specific?
Sure. I didn't
At 3:47 +0200 10/8/04, Przemyslaw Popielarski wrote:
Paul DuBois [EMAIL PROTECTED] wrote:
User variables do not work with REGEXP under MySQL 4.0.21 4.1.5.
Is this a bug or a feature?
It's difficult to provide an answer to this because you're providing
no information about what do not work
Paul DuBois [EMAIL PROTECTED] wrote:
SELECT
@a:=FIRMLEGALZIPCODE
FROM tCustomers
WHERE @a REGEXP [0-9]
- Empty set (0.03 sec)
You're expecting the value to be selected first so that you then can
test it with the WHERE clause later.
Of course you're right. Thanks. According to your
At 4:01 +0200 10/8/04, Przemyslaw Popielarski wrote:
Paul DuBois [EMAIL PROTECTED] wrote:
SELECT
@a:=FIRMLEGALZIPCODE
FROM tCustomers
WHERE @a REGEXP [0-9]
- Empty set (0.03 sec)
You're expecting the value to be selected first so that you then can
test it with the WHERE clause later.
Of
I would like to do something like this in the mysql client
Select CourseId
Into @CourseId
From Course
Where CourseCd='ENGL';
I also tried
@CourseId = select CourseId from Course where CourseCd = 'ENGL';
Neither syntax works. So I am wondering if there is a way to assign
values using SQL in
You have the wrong syntax. You need
SELECT @CourseId:= CourseId FROM Course WHERE CourseId='ENGL';
See the manual http://dev.mysql.com/doc/mysql/en/Variables.html for more.
Michael
Boyd E. Hemphill wrote:
I would like to do something like this in the mysql client
Select CourseId
Into @CourseId
Hi everybody.
I generally try to parametrize out my queries as much as possible with
user-variables. So, say you have a general query for all users:
---
SELECT ...
FROM users
---
It's nice to do this:
SET @USER_ID:= NULL;
SELECT ...
FROM users
WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED
Hi everybody.
I often try to parametrize out my queries as much as possible with
user-variables. So, say you have a general query for all users:
---
SELECT ...
FROM users
---
It's nice to do this:
SET @USER_ID:= NULL;
SELECT ...
FROM users
WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED],1
well, it seems to be fine without SUM and GROUP BY...
E.g.,
SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ...
produces expected results.
Emmett Bishop wrote:
Vadim,
if I'm not mistaken, you can't set a variable then use
it in the same statement.
See
P. [EMAIL PROTECTED]
To: Emmett Bishop [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: Re: User variables + SUM + GROUP BY = strange behavior
Date: Fri, 16 Apr 2004 05:50:12 -0400
well, it seems to be fine without SUM and GROUP BY...
E.g.,
SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED
Hello all,
Could anyone comment on User Variable behavior in the example below?
Thanks,
Vadim.
=
mysql SELECT
- LEFT(CallTime,10) AS CallDate,
- @a := SUM(Charge),
- @b := SUM(Cost),
-
Sorry, the message got garbled, here is a more digestible look:
-Original Message-
Hello all,
Could anyone comment on User Variable behavior in the example below?
Thanks,
Vadim.
=
mysql SELECT
-
Vadim,
if I'm not mistaken, you can't set a variable then use
it in the same statement.
See http://dev.mysql.com/doc/mysql/en/Variables.html
A little ways down the page...
The general rule is to never assign and use the same
variable in the same statement.
-- Tripp
--- Vadim P. [EMAIL
These are the statements that are currently giving me the problems.
SELECT @main_id:=ID, @FCSecID:=FCSecID, @Periodicity:=Periodicity,
@EOP:=EOP FROM main_data_temp WHERE checked=0 ORDER BY fcsecid ASC LIMIT
1;
SELECT * FROM actuals WHERE [EMAIL PROTECTED] AND Period LIKE
'[EMAIL PROTECTED]'; #
Hi Guys,
Do you know what version user variables were introduced? I can't seem to
get them working in 3.22 and yet they work in 3.23.
Best regards,
Bruno Mustone
At 12:05 PM + 11/17/03, Bruno Mustone wrote:
Hi Guys,
Do you know what version user variables were introduced? I can't seem to
get them working in 3.22 and yet they work in 3.23.
They were introduced in 3.23.6.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB
Hmmm they seem to work in version 3.23.57.
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: 17 November 2003 18:38
To: Bruno Mustone; [EMAIL PROTECTED]
Subject: Re: User Variables
At 12:05 PM + 11/17/03, Bruno Mustone wrote:
Hi Guys,
Do you know what version
; [EMAIL PROTECTED]
Subject: Re: User Variables
At 12:05 PM + 11/17/03, Bruno Mustone wrote:
Hi Guys,
Do you know what version user variables were introduced? I can't seem
to get them working in 3.22 and yet they work in 3.23.
They were introduced in 3.23.6.
--
Paul DuBois, Senior Technical
Hi group,
Is there anything I need to set in MySQL in order to use USER variables?
Thanks,
__
NZEYIMANA Emery Fabrice
NEFA Computing Services, Inc.
P.O. Box 5078 Kigali
Office Phone: +250-51 11 06
Office Fax: +250-50 15 19
Mobile: +250-08517768
Email: [EMAIL
Director General: NEFACOMP [EMAIL PROTECTED] wrote:
Is there anything I need to set in MySQL in order to use USER variables?
No. What is wrong with user variables for you?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net
I get the following strange behavior with a user variable. @T has the value
0 to start; after adding 1 to @T a few times, it ends up with a clearly
incorrect value. I'd expect it to have a value of 280 after the second
select.
--
SELECT @T
--
+--+
| @T |
+--+
I would like to be able to set a user variable on startup of MySQL. I
have review the documentation and searched the archives and I haven't
seen anything about being able to do, or not do this.
Can this be done? Since I'm unable to find reference to it, my guess is
no, but I thought I'd give
Brawley [EMAIL PROTECTED]
wrote:
Not just MySQLFront. PhpMyAdmin chokes on it, too.
-
- Original Message -
From: ML
To: [EMAIL PROTECTED]
Sent: Tuesday, May 27, 2003 9:08 AM
Subject: Re: User Variables doesn't work?
You are right, I tried from command
Not just MySQLFront. PhpMyAdmin chokes on it, too.
-
- Original Message -
From: ML
To: [EMAIL PROTECTED]
Sent: Tuesday, May 27, 2003 9:08 AM
Subject: Re: User Variables doesn't work?
You are right, I tried from command line and it works, the problem occurs
with MySQL
on it, too.
-
- Original Message -
From: ML
To: [EMAIL PROTECTED]
Sent: Tuesday, May 27, 2003 9:08 AM
Subject: Re: User Variables doesn't work?
You are right, I tried from command line and it
works, the problem occurs
with MySQL Front v2.5. This is strange
It seems like MyODBC (or ODBC in general ?) cannot accept user variables.
The following query works OK when quering directly through the API but doesnt work via
MyODBC
Any suggestions to some workaround allowing the use of user variables???
SELECT id,
@s:=lang lang=da,
@o
with it until I can make it deadlock?)
* In section 4.10.4 it says Update queries that use user variables are
not replication-safe (yet)
-- Is this still the case?
-- If so, will this be fixed in the near future?
My plan is to store a list of update statements that together will
form
At 12:19 -0400 5/9/02, Luc Foisy wrote:
hmm. ok i see what you are getting at
but you do that every time you use a where clause, do you not?
No, not really
SELECT value FROM table WHERE value = 5
In this case value is used in the WHERE clause to determine which records
to select. Once
At 11:57 -0400 5/9/02, Luc Foisy wrote:
It may not make any sense to you, but it makes perfect sense to me
Okay. Then it would seem that either:
- I understand what you want to do, in which case you can't do it, because
you're using SQL variables in a self-contradictory way.
- I don't
I have been trying to use 'user variables' to keep
track of the previous row for use in a calculation of
the present row. Is there a way I can do this? Or
is there a better way in trying to use a previous
rows value in the present row.
For example;
SELECT number as current_day, (number
Jasmin,
Thursday, May 02, 2002, 5:00:14 PM, you wrote:
JB I have been trying to use 'user variables' to keep
JB track of the previous row for use in a calculation of
JB the present row. Is there a way I can do this? Or
JB is there a better way in trying to use a previous
JB rows value
Hi,
Jasmin Bertovic wrote:
cut
For example;
SELECT number as current_day, (number - prevnumber)
as change_from_prev_day FROM TABLE ORDER BY DATE
prevnumber is the reference that I need from the
previous row.
Am I missing something simple or do I have to do this
outside of MYSQL?
1. Yes you
This makes sence, now that I have had some time to
think about it. If I self join, I have to make sure
that my data is continuous to shift all the rows by 1.
Some of the things could have missing dates or maybe
I could self join on an incremental counter using a
tmp table. I have do go back to
You have this table:
id data
3 2
2 1
1 3
This query:
SELECT @var, data, @var:=data+(IFNULL(@var,0)) FROM table
What would result would you expect the first time you execute the query?
I would expect this:
NULL, 2, 2
2, 1, 3
3, 3, 6
But this is what you get the first time:
NULL, 2, 2
NULL, 1,
Perhaps I should have split this up into two mails, but I saw them related
in the difference of databases
On mysql Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686)
this works
SELECT SQL_BUFFER_RESULT DATE_FORMAT(ORDERHEADER.AvailableAt,'%y/%m/%d') AS
'Date',
Perhaps I should have split this up into two mails, but I saw them related
in the difference of databases
On mysql Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686)
this works
SELECT SQL_BUFFER_RESULT DATE_FORMAT(ORDERHEADER.AvailableAt,'%y/%m/%d') AS
'Date',
At 18:32 +0100 1/24/02, Peter Bremer wrote:
Hi,
Can anybody explain why the following does not work?
SELECT @variable := value1;
UPDATE table SET field1 = @variable WHERE field2 = value2;
The result is always that field1 is set to NULL...
Works for me, if I substitute a real value for
Ooops!! This seems to be a bug in EMS MySQL Manager... Sorry to bother
you all...
Regards
Peter
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: do 24 jan 2002 18:51
To: Peter Bremer; Mysql (E-mail)
Subject: Re: Problem with UPDATE and User Variables
Hi
I'm trying to use user variables such as @myvar within a query which I've
got running perfectly on a 3.23.35a server , however, i can't get the same
query to run on 3.22.32 server :(
Does anyone know if 3.22.32 supports user variables ?
Thanks
Girish
Hi
I'm trying to use user variables such as @myvar within a query which I've
got running perfectly on a 3.23.35a server , however, i can't get the same
query to run on 3.22.32 server :(
Does anyone know if 3.22.32 supports user variables ?
3.23.6 was the first version to support UDVs
By reading the online manual-split.tar, I try to do some examples but one
doesn't work.
(For this time, I didn't configure mysql).
To (re)-product this error:
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT
Hello,
My problem is about users variables. I want to do
a script to create a database so:
SET @dbname:=mydbname;
CREATE DATABASE @dbname;# -- Don´t
work
CREATE DATABASE (SELECT @dbname);# -- Don´t
Work
I have tried to define @dbname=mydbname directly
but it blocks.
At 3:16 AM +0200 6/13/01, Luis A. Fdez. de Retana Aguirre wrote:
Hello,
My problem is about users variables. I want to do
a script to create a database so:
SET @dbname:=mydbname;
CREATE DATABASE @dbname;# -- Don´t
work
CREATE DATABASE (SELECT @dbname);# -- Don´t
Work
Basil Hussain writes:
Hi all,
Is there any reason why I can't do something like any of the following?
SET @blah := SHOW VARIABLES LIKE 'server_id';
SHOW @blah := VARIABLES LIKE 'server_id';
It doesn't seem to work no matter which combinations of syntax I try.
Is setting user
Hi,
Are there any issues with user variables ( SET @variable:=) overflowing
memory on the server?
At any one
time I wouldn't need most of them, but I don't see any way of
removing them
once set. Might this be an issue, or is there some garbage collection
and/or memory limit
Are there any issues with user variables ( SET @variable:=) overflowing
memory on the server?
I am considering using user variables to implement a simple psuedo stored
procedure API whereby an API user can set variables for a query in one
function, and call the query later on in another
Gary Shea writes:
cut
Sorry, this is my fault for not describing the problem better.
Here's the result I get with DISTINCT (I've given the columns
names to make the table smaller, but not changed the SQL from what
I sent in the bug report):
+---+
| i |
+---+
| 1 |
| 2 |
[EMAIL PROTECTED] writes:
Description:
It appears that if a column generated by a select is
a sum of variables generated in other columns of the
select (see example below!), the column will have a
bogus result if the select is 'DISTINCT'. If the
DISTINCT
How many Bytes can I save in a user variable?
Because I have the following strategy:
I want to save all PrimaryKeys in a user variable
SELECT @a:=' ';
SELECT @a := CONCAT(@a, ', ', TPrimKey) FROM table WHERE condition;
so it is very important to know how many bytes can be stored.
regards
86 matches
Mail list logo
