No, MySQL does not support subquery so you need work around (not use)
it, by creating temp tbl and/or join etc. Think it a diff. SQL style.
Yan Zhang wrote:
>
> Please help me here, and let me know if MySQL can do it or not. All people
> around me told it should work in SyBASE.
>
> Yan Zhang
> [EMAIL PROTECTED]
>
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> I have two tables:
>
> mysql> show tables;
> +------------------+
> | Tables_in_vendor |
> +------------------+
> | savedata |
> | tradeday |
> +------------------+
> 5 rows in set (0.01 sec)
>
> mysql> describe tradeday;
> +-------------+------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+------+------+-----+---------+-------+
> | tradedate | date | YES | | NULL | |
> | previousday | date | YES | | NULL | |
> +-------------+------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
>
> mysql> describe savedata;
> +------------+---------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+---------------+------+-----+---------+-------+
> | tradedate | date | YES | | NULL | |
> | sym | char(9) | | | | |
> | cusip | char(10) | YES | | NULL | |
> | closeprice | float | YES | | NULL | |
> | drm_share | float | YES | | NULL | |
> | indx | char(10) | YES | | NULL | |
> | weight | double(20,15) | YES | | NULL | |
> | closedate | date | YES | | NULL | |
> +------------+---------------+------+-----+---------+-------+
> 8 rows in set (0.00 sec)
>
> mysql> select distinct tradedate from savedata ;
> +------------+
> | tradedate |
> +------------+
> | 0000-00-00 |
> +------------+
> 1 row in set (0.03 sec)
>
> mysql> select * from savedata limit 2;
> +------------+------+-----------+------------+-----------+-------+----------
> ---------+------------+
> | tradedate | sym | cusip | closeprice | drm_share | indx | weight
> | closedate |
> +------------+------+-----------+------------+-----------+-------+----------
> ---------+------------+
> | 0000-00-00 | ABT | 002824100 | 51.4 | 1546.59 | sp500 |
> 0.172393452280000 | 2001-05-25 |
> | 0000-00-00 | ADCT | 000886101 | 8.6 | 777.31 | sp500 |
> 0.086644489470000 | 2001-05-25 |
> +------------+------+-----------+------------+-----------+-------+----------
> ---------+------------+
> 2 rows in set (0.01 sec)
>
> mysql> select * from tradeday where previousday = '2001-05-25';
> +------------+-------------+
> | tradedate | previousday |
> +------------+-------------+
> | 2001-05-29 | 2001-05-25 |
> +------------+-------------+
> 1 row in set (0.03 sec)
>
> HERE ARE THE QUERY I TRIED:
>
> mysql> update savedata SET tradedate='1' where savedata.closedate =
> tradeday.previousday;
> ERROR 1109: Unknown table 'tradeday' in where clause
> mysql> update savedata SET tradedate='1' from tradeday, savedata where
> savedata.closedate = tradeday.previousday;
> ERROR 1064: You have an error in your SQL syntax near 'from tradeday,
> savedata where savedata.closedate = tradeday.previousday' at line 1
> mysql> update savedata SET tradedate='2001-01-01' from tradeday, savedata
> where savedata.closedate = tradeday.previousday;
> ERROR 1064: You have an error in your SQL syntax near 'from tradeday,
> savedata where savedata.closedate = tradeday.previousday' at line 1
> mysql> update savedata SET tradedate='2001-01-01' from tradeday a, savedata
> b where b.closedate = a.previousday;
> ERROR 1064: You have an error in your SQL syntax near 'from tradeday a,
> savedata b where b.closedate = a.previousday' at line 1
> mysql> update savedata SET tradedate='2001-01-01' from tradeday a, savedata
> b where a.previousday=b.closedate;
> ERROR 1064: You have an error in your SQL syntax near 'from tradeday a,
> savedata b where a.previousday=b.closedate' at line 1
> mysql> update savedata SET weight='1' from tradeday a, savedata b where
> a.previousday = b.closedate;
> ERROR 1064: You have an error in your SQL syntax near 'from tradeday a,
> savedata b where a.previousday = b.closedate' at line 1
> mysql> update savedata SET weight='1.0' from tradeday a, savedata b where
> a.previousday = b.closedate;
> ERROR 1064: You have an error in your SQL syntax near 'from tradeday a,
> savedata b where a.previousday = b.closedate' at line 1
> mysql> update savedata SET weight='1.0' where tradeday.previousday =
> savedata.closedate;
> ERROR 1109: Unknown table 'tradeday' in where clause
> mysql> update savedata SET weight='1' where select tradeday.tradedate from
> tradeday,savedata where savedata.closedate = tradeday.previousday;
> ERROR 1064: You have an error in your SQL syntax near 'select
> tradeday.tradedate from tradeday,savedata where savedata.closedate = trad'
> at line 1
> mysql> update savedata SET weight='1' where savedata.closedate in (select
> tradeday.tradedate from tradeday,savedata where savedata.closedate =
> tradeday.previousday);
> ERROR 1064: You have an error in your SQL syntax near 'select
> tradeday.tradedate from tradeday,savedata where savedata.closedate = tra' at
> line 1
> mysql> update savedata SET weight='1' where savedata.closedate = (select
> max( tradeday.tradedate) from tradeday,savedata where savedata.closedate =
> tradeday.previousday);
> ERROR 1064: You have an error in your SQL syntax near 'select max(
> tradeday.tradedate) from tradeday,savedata where savedata.closedate ' at
> line 1
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
