Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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