Using variable to pass value between sql statement : Variable « Select Clause « SQL / MySQL






  1. SQL / MySQL
  2. Select Clause
  3. Variable

Using variable to pass value between sql statement

       
mysql>
mysql>
mysql> CREATE TABLE EmployeeS(
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          NAME           CHAR(15)     NOT NULL,
    ->          INITIALS       CHAR(3)      NOT NULL,
    ->          BIRTH_DATE     DATE                 ,
    ->          SEX            CHAR(1)      NOT NULL,
    ->          JOINED         SMALLINT     NOT NULL,
    ->          STREET         VARCHAR(30)  NOT NULL,
    ->          HOUSENO        CHAR(4)              ,
    ->          POSTCODE       CHAR(6)              ,
    ->          TOWN           VARCHAR(30)  NOT NULL,
    ->          PHONENO        CHAR(13)             ,
    ->          LEAGUENO       CHAR(4)              ,
    ->          PRIMARY KEY    (EmployeeNO)           );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (2, 'Jack', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (6, 'Link', 'R', '1964-06-25', 'M', 1977, 'Street1','80', '1234KK', 'Stratford', '070-476537', '8467');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'First Way','39', '9758VB', 'Stratford', '070-347689', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (8, 'Mary', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (27, 'Smith', 'DD', '1964-12-28', 'F', 1983, 'Street2','804', '8457DK', 'Eltham', '079-234857', '2513');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (28, 'Smith', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'First Way','16', '4377CB', 'Stratford', '070-473458', '6409');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Main Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (100, 'Link', 'P', '1963-02-28', 'M', 1979, 'Street1','80', '6494SG', 'Stratford', '070-494593', '6524');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (104, 'Jane', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EmployeeS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql>
mysql> SELECT   @EmployeeNO := EmployeeNO
    -> FROM     EmployeeS
    -> ORDER BY EmployeeNO DESC;
+---------------------------+
| @EmployeeNO := EmployeeNO |
+---------------------------+
|                       112 |
|                       104 |
|                       100 |
|                        95 |
|                        83 |
|                        57 |
|                        44 |
|                        39 |
|                        28 |
|                        27 |
|                         8 |
|                         7 |
|                         6 |
|                         2 |
+---------------------------+
14 rows in set (0.00 sec)

mysql>
mysql> SELECT   @EmployeeNO;
+-------------+
| @EmployeeNO |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT   @PNR7 := 7
    -> FROM     EmployeeS
    -> WHERE    EmployeeNO < @PNR7;
Empty set (0.00 sec)

mysql>
mysql> drop table Employees;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Variables can be assigned values from arbitrary expressions.
2.Using variable the calculate the new column ID
3.Global System Variables versus System Variables at the Connection Level
4.Using SQL Variables in Queries
5.Returns all global variables and their settings
6.Return the session system variables
7.If you're retrieving the value of a global variable, you must also precede the variable name with the global keyword
8.If you want to retrieve a value for a session variable, rather than a global variable
9.Set the query_cache_limit variable to 1000000 at the global level
10.To set a session-level variable
11.Check the state of the variables with the following command:
12.Ordinary variables indicated by a prefixed @ sign.
13.System and server variables: Such variables contain states or attributes of the MySQL server.
14.Variable Assignment
15.Use of Variables
16.SQL variables hold single values.
17.If the variable has not been used previously, that value is NULL:
18.To set a variable explicitly to a particular value, use a SET statement.
19.Variable names are case sensitive:
20.SQL variables may be used to store the results of intermediate calculations.
21.Use a two-stage approach involving one query that selects the maximum size into a SQL variable, and another th
22.Calculate the overall average and save it in a variable, then compare each driver's average to the saved value
23.To use a SQL variable, store the highest price in it, then use the variable to identify the record containing
24.To find a value and save it in a variable
25.SELECT statement defines two variables
26.User Variables
27.Set a variable specifically.
28.Create the user variable PLAYERNO and initialize it with the value 7.
29.Can the result of a SELECT statement be assigned to a user variable
30.Compare decimal type variables
31.Create and set MySQL variable
32.Using concat to create value for variable
33.Using variable in where clause
34.Assin constant value to a variable
35.Assign the result from a sql to a variable
36.Assign a constant to a variable
37.Assign and create three variables inside one select statement
38.Assign the result of an aggregate function to a variable
39.Compare to a variable
40.Insert statement with variable
41.Assign value to a variable with :=
42.Using select ... into to assign value to variabls
43.A two-query approach
44.Advancing the rank only when values change
45.Use the differential as follows to produce team standings that include winning percentage and GB values
46.To calculate a standard deviation based on n-1 degrees of freedom instead
47.Calculating Linear Regressions or Correlation Coefficients
48.Assigning Ranks
49.Comparing a Table to Itself