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
