Define a variable within select and use it within the same select

by

MySQL documentation is quite clear on this:

As a general rule, you should never assign a value to a user variable
and read the value within the same statement. You might get the
results you expect, but this is not guaranteed. The order of
evaluation for expressions involving user variables is undefined and
may change based on the elements contained within a given statement;
in addition, this order is not guaranteed to be the same between
releases of the MySQL Server. In SELECT @a, @a:=@a+1, …, you might
think that MySQL will evaluate @a first and then do an assignment
second. However, changing the statement (for example, by adding a
GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an
execution plan with a different order of evaluation.

You can do what you want using a subquery:

select @z, @z*2
from (SELECT @z:=sum(item)
      FROM TableA
     ) t;

More Related Contents:

  1. Get top n records for each group of grouped results
  2. Why the order of evaluation for expressions involving user variables is undefined?
  3. Should I use the datetime or timestamp data type in MySQL?
  4. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  5. MySQL string replace
  6. What is the difference between utf8mb4 and utf8 charsets in MySQL?
  7. How do I find the MySQL my.cnf location
  8. How do I get the current time zone of MySQL?
  9. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  10. MySQL select one column DISTINCT, with corresponding other columns
  11. How to create linked server MySQL
  12. Group mysql query by 15 min intervals
  13. MySQL FULL JOIN?
  14. How to fill in the “holes” in auto-increment fields?
  15. Using Count to find the number of occurrences
  16. Is there any reason to worry about the column order in a table?
  17. MySQL Incorrect datetime value: ‘0000-00-00 00:00:00’
  18. How to change MySQL table names in Linux server to be case insensitive?
  19. MySQL command line formatting with UTF8
  20. MYSQL select DISTINCT values in two columns
  21. MySQL UPDATE and SELECT in one pass
  22. Use promise to process MySQL return value in node.js
  23. where does MySQL store database files?
  24. How can I achieve initcap functionality in MySQL?
  25. The web application [] appears to have started a thread named [Abandoned connection cleanup thread] com.mysql.jdbc.AbandonedConnectionCleanupThread
  26. Execute INSERT if table is empty?
  27. MySQL: Querying for unicode entities
  28. undefined method `eq’ for nil:NilClass with rails 3 and ruby enterprise on ubuntu hardy
  29. MySQL data directory location
  30. INSERT INTO fails with node-mysql

Leave a Comment