MySQL: Set user variable from result of query

by

Yes, but you need to move the variable assignment into the query:

SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;

Test case:

CREATE TABLE user (`user` int, `group` int);
INSERT INTO user VALUES (123456, 5);
INSERT INTO user VALUES (111111, 5);

Result:

SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;

+--------+-------+
| user   | group |
+--------+-------+
| 123456 |     5 |
| 111111 |     5 |
+--------+-------+
2 rows in set (0.00 sec)

Note that for SET, either = or := can be used as the assignment operator. However inside other statements, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements.

UPDATE:

Further to comments below, you may also do the following:

SET @user := 123456;
SELECT `group` FROM user LIMIT 1 INTO @group; 
SELECT * FROM user WHERE `group` = @group;

More Related Contents:

  1. how to delete data from table A besides the customer service and director data in table B?
  2. Get records with max value for each group of grouped SQL results
  3. Get the new record primary key ID from MySQL insert query?
  4. How to properly create composite primary keys – MYSQL
  5. Is there a MySQL option/feature to track history of changes to records?
  6. MySQL: multiple tables or one table with many columns? [closed]
  7. Add 2 hours to current time in MySQL?
  8. How can I Insert many rows into a MySQL table and return the new IDs?
  9. What is cardinality in MySQL?
  10. How to prepend a string to a column value in MySQL?
  11. MySQL trigger On Insert/Update events
  12. How to get first day of every corresponding month in mysql?
  13. How can I import a large (14 GB) MySQL dump file into a new MySQL database?
  14. How can I change MariaDB to MySQL in XAMPP?
  15. MySQL: LIMIT by a percentage of the amount of records?
  16. MySql: Tinyint (2) vs tinyint(1) – what is the difference?
  17. MySQL Enum performance advantage?
  18. Dynamic table name at sql statement
  19. MySQL Inner Join Query Multiple Tables
  20. updating columns with a sequence number mysql
  21. How repair corrupt xampp ‘mysql.user’ table?
  22. Select a column with a keyword name
  23. Sum results of a few queries and then find top 5 in SQL
  24. Select all months within given date span, including the ones with 0 values
  25. ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  26. Use Composite Primary Key as Foreign Key
  27. MySQL: set field default value to other column
  28. CREATE command denied to user？
  29. What is the MAX number if I store int(255) in MySQL?
  30. Deleting a row based on the max value

Leave a Comment