MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Boyd E. Hemphill Date:July 27 2004 3:58pm
Subject:assigning values to user variables in the mysql client
View as plain text  
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 the mysql client.  This would be _very_ handy for
scripting.

Here is what I produced to get around the problem.  It is a bit
convoluted but if you are wishing for this functionality (and it does
not yet exist) it will do the trick:

insert into Course 
values(null, 21, 14, '', 99, 'Not Assigned', 0, 0)
;
select 
   concat( 'set sql_auto_is_null = 1; ',
           'set @CourseId = ', CourseId,'; ',
           'update Test set CourseId = @UniCourseId where Title like
\'SAT%\'; '
         )
   into outfile 'update_test.sql'
   from Course
  where CourseId is null
;
source /var/lib/mysql/db_name/update_unitest.sql
;
set sql_auto_is_null = 0
;

Be sure to clean up your data directory by deleting the file created as
select ... into outfile places files into the diretory of the db on
which the query is entered

Boyd E. Hemphill



Thread
Innodb table space getting filled up without any increase in actual rows!!Sp.Raja3 Mar
Re: Innodb table space getting filled up without any increase in actual rows!!Heikki Tuuri3 Mar
  • assigning values to user variables in the mysql clientBoyd E. Hemphill27 Jul
    • Re: assigning values to user variables in the mysql clientMichael Stassen27 Jul
Re: Innodb table space getting filled up without any increase in actual rows!!Sp.Raja4 Mar
Re: Innodb table space getting filled up without any increase in actual rows!!Heikki Tuuri4 Mar
Re: Innodb table space getting filled up without any increase in actual rows!!Sp.Raja5 Mar
  • Re: Innodb table space getting filled up without any increase in actual rows!!Harald Fuchs5 Mar
  • Re: Innodb table space getting filled up without any increase in actual rows!!Heikki Tuuri5 Mar
    • row lockingSahil Aggarwal16 Apr
      • Need help with indexing !jeroen clarysse16 Apr
      • Re: row lockingPaul DuBois16 Apr
        • Re: row lockingSahil Aggarwal19 Apr
RE: row lockingVictor Pendleton16 Apr
RE: Need help with indexing !Victor Pendleton16 Apr