2

How do I set the @DATE variable by using a table/column reference?

Right now I've set it manually as you can see below with the opening:

SET @DATE = '2012-03-31 01:00:00';

The table from which I would pull the date variable is not the table from which the SELECT statement is pulling.

SQL query mockup:

SET @DATE = '2012-03-31 01:00:00';

SELECT
  Column 1
FROM
  Table
WHERE
  table.column = @DATE
2

If I understand your question correctly, there are two ways that I can think of:

mysql> SET @date = (SELECT source_col FROM source_table WHERE whatever_condition); 
Query OK, 1 row affected (0.03 sec)

or

mysql> SELECT source_col FROM source_table WHERE whatever_condition INTO @date;
Query OK, 1 row affected (0.03 sec)

These two are functionally equivalent when only one value is involved, but the second form can also be expanded to handle multiple values.

mysql> SELECT col1, col2 FROM source_tbl WHERE whatever_condition INTO @var1, @var2;

But your question involved using the resulting @date in your query.

mysql> SELECT * 
         FROM target_table 
        WHERE target_column = @date;

If that's the intended purpose, you can eliminate the variable entirely and use the first example, above, as a subquery inside the final query.

mysql> SELECT * 
         FROM target_table 
        WHERE target_column = (SELECT source_col FROM source_table 
                                WHERE whatever_condition);

http://dev.mysql.com/doc/refman/5.5/en/comparisons-using-subqueries.html

  • Nice! I used your first example and it works perfectly: mysql> SET @date = (SELECT source_col FROM source_table WHERE whatever_condition); – smileyseven Sep 23 '12 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.