SQL Query - Set Date Variable by Table/Column Reference

Question

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

Answer 1

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

• LoveYouFyi: Nice! I used your first example and it works perfectly: mysql> SET @date = (SELECT source_col FROM source_table WHERE whatever_condition)