- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Set user variable from result of query in MySQL?
To set user variable from result of query in MySQL, you need to move that variable into the assignment.
To understand the above concept, let us first create a table. The query to create a table is as follows −
mysql> create table UserVariable -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into UserVariable values(101,'John'); Query OK, 1 row affected (0.17 sec) mysql> insert into UserVariable values(200,'Tom'); Query OK, 1 row affected (0.18 sec) mysql> insert into UserVariable values(500,'Carol'); Query OK, 1 row affected (0.13 sec)
Display all records from the table using select command. The query is as follows −
mysql> select *from UserVariable;
+------+-------+ | Id | Name | +------+-------+ | 101 | John | | 200 | Tom | | 500 | Carol | +------+-------+ 3 rows in set (0.00 sec)
Write a query with a user variable that display the records with maximum id. First, I am going to create a variable and initialize this variable by selecting maximum id from the above table. The query is as follows −
mysql> set @Maxid=(select MAX(Id) from UserVariable); Query OK, 0 rows affected (0.00 sec)
After that, create another variable which has only name with that particular maximum id. The query is as follows −
mysql> set @Name=(select Name from UserVariable where Id=@Maxid); Query OK, 0 rows affected (0.00 sec)
Now you can check what value is present in variable Name. The query is as follows −
mysql> select @Name;
The following is the output that displays the name with the highest Id −
+-------+ | @Name | +-------+ | Carol | +-------+ 1 row in set (0.00 sec)
- Related Articles
- Set the result of a query to a variable in MySQL?
- MongoDB query to set user defined variable into query?
- How can we use SET statement to assign a SELECT result to a MySQL user variable?
- How to store Query Result in a variable using MySQL?
- How to assign the result of a MySQL query into a variable?
- Set user-defined variable with table name in MySQL prepare statement?
- Assign an SQL result to variable from prepared statement in MySQL?
- MySQL query to get result from multiple select statements?
- MySQL CREATE USER with a variable?
- Perform MySQL SELECT INTO user-defined variable
- What is the meaning of ‘empty set’ in MySQL result set?
- Set custom messages by working with MySQL IF Statements and SELECT in a user-defined variable
- MySQL query to sum 3 different values in a column displaying total of each value in result set?
- Select into a user-defined variable with MySQL
- MySQL ORDER BY with numeric user-defined variable?