SQL
--
Questions
--
Followers
Top Experts
Problem with a MySQL UPDATE using INNER JOIN and GROUP BY
I have two tables 'a' and 'b' which I need to use to update table 'c'. When I execute the code below I get and error message: ERROR 1111 (HY000): Invalid use of group function
I have spent 3 hours trying to figure this out. could someone please show me the correct syntax? What am I not understanding?
Thanks!
UPDATE `ma_trend_spread` AS a
INNER JOIN countries AS c ON letter_code = 'US'
JOIN (
select fk_curr, fk_tfid, fk_tdate
from `adpmaprices`
where trend = '1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD'
GROUP BY fk_tdate
) as b ON b.fk_tdate >= '2011-01-01'
SET a.fk_curr = b.fk_curr, a.fk_tfid = b.fk_tfid, a.fk_tdate = b.fk_tdate, a.fk_countryid = c.id,
a.model = 'CL', a.trend_up_cnt = COUNT(b.trend)
;
IF I am doing an INSERT these both work as expected.
INSERT IGNORE INTO ma_trend_spread (fk_curr, fk_tfid, fk_tdate, fk_countryid, model, trend_dn_cnt )
SELECT fk_curr, fk_tfid, fk_tdate, countries.id, 'CL', COUNT(trend)
FROM adpmaprices
INNER JOIN countries ON
trend = '-1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD' AND letter_code = 'US'
GROUP BY fk_tdate;
INSERT IGNORE INTO ma_trend_spread (fk_curr, fk_tfid, fk_tdate, fk_countryid, model, trend_up_cnt )
SELECT fk_curr, fk_tfid, fk_tdate, countries.id, 'CL', COUNT(trend)
FROM adpmaprices
INNER JOIN countries ON
trend = '1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD' AND letter_code = 'US'
GROUP BY fk_tdate;
I have spent 3 hours trying to figure this out. could someone please show me the correct syntax? What am I not understanding?
Thanks!
UPDATE `ma_trend_spread` AS a
INNER JOIN countries AS c ON letter_code = 'US'
JOIN (
select fk_curr, fk_tfid, fk_tdate
from `adpmaprices`
where trend = '1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD'
GROUP BY fk_tdate
) as b ON b.fk_tdate >= '2011-01-01'
SET a.fk_curr = b.fk_curr, a.fk_tfid = b.fk_tfid, a.fk_tdate = b.fk_tdate, a.fk_countryid = c.id,
a.model = 'CL', a.trend_up_cnt = COUNT(b.trend)
;
IF I am doing an INSERT these both work as expected.
INSERT IGNORE INTO ma_trend_spread (fk_curr, fk_tfid, fk_tdate, fk_countryid, model, trend_dn_cnt )
SELECT fk_curr, fk_tfid, fk_tdate, countries.id, 'CL', COUNT(trend)
FROM adpmaprices
INNER JOIN countries ON
trend = '-1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD' AND letter_code = 'US'
GROUP BY fk_tdate;
INSERT IGNORE INTO ma_trend_spread (fk_curr, fk_tfid, fk_tdate, fk_countryid, model, trend_up_cnt )
SELECT fk_curr, fk_tfid, fk_tdate, countries.id, 'CL', COUNT(trend)
FROM adpmaprices
INNER JOIN countries ON
trend = '1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD' AND letter_code = 'US'
GROUP BY fk_tdate;
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
You're switching the order:
UPDATE a
SET a.fk_curr = b.fk_curr, a.fk_tfid = b.fk_tfid, a.fk_tdate = b.fk_tdate, a.fk_countryid = c.id,
a.model = 'CL', a.trend_up_cnt = COUNT(b.trend)
FROM `ma_trend_spread` AS a
INNER JOIN countries AS c ON letter_code = 'US'
JOIN (
select fk_curr, fk_tfid, fk_tdate
from `adpmaprices`
where trend = '1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD'
GROUP BY fk_tdate
) as b ON b.fk_tdate >= '2011-01-01'
UPDATE a
SET a.fk_curr = b.fk_curr, a.fk_tfid = b.fk_tfid, a.fk_tdate = b.fk_tdate, a.fk_countryid = c.id,
a.model = 'CL', a.trend_up_cnt = COUNT(b.trend)
FROM `ma_trend_spread` AS a
INNER JOIN countries AS c ON letter_code = 'US'
JOIN (
select fk_curr, fk_tfid, fk_tdate
from `adpmaprices`
where trend = '1' and fk_tdate >= '2011-01-01' AND fk_tfid = '1' AND fk_curr = 'USD'
GROUP BY fk_tdate
) as b ON b.fk_tdate >= '2011-01-01'
I am sorry but I am getting the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `ma_trend_spread` AS a
INNER JOIN countries AS c ON letter_code = 'US'
JOIN' at line 4
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `ma_trend_spread` AS a
INNER JOIN countries AS c ON letter_code = 'US'
JOIN' at line 4
Well, I can't be sure of your syntax. But as a rule, this is how you should build your update query. First create a select query that returns the results you want:
SELECT *
FROM table AS t1 INNER JOIN table2 AS t2 etc...
WHERE ..... (optional)
Once that's done, you replace the SELECT clause:
UPDATE t1 (here you specify which table you want to update)
SET t1.field='fieldvalue'
FROM ..... same as before.
SELECT *
FROM table AS t1 INNER JOIN table2 AS t2 etc...
WHERE ..... (optional)
Once that's done, you replace the SELECT clause:
UPDATE t1 (here you specify which table you want to update)
SET t1.field='fieldvalue'
FROM ..... same as before.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
This works:
SELECT fk_curr, fk_tfid, fk_tdate, countries.id, 'CL', COUNT(trend)
FROM adpmaprices
INNER JOIN countries ON
trend = '1' and fk_tdate >= '2011-07-01' AND fk_tfid = '1' AND fk_curr = 'USD' AND letter_code = 'US'
GROUP BY fk_tdate;
This does not:
UPDATE ma_trend_spread AS c
SET c.fk_curr = a.fk_curr, c.fk_tfid = a.fk_tfid, c.fk_tdate = a.fk_tdate, c.fk_countryid = b.id, c.model = 'CL', c.trend_up_cnt = , COUNT(a.trend)
FROM adpmaprices AS a
INNER JOIN countries AS b ON
a.trend = '1' and a.fk_tdate >= '2011-07-01' AND a.fk_tfid = '1' AND a.fk_curr = 'USD' AND b.letter_code = 'US'
GROUP BY a.fk_tdate;
I am still getting an error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' COUNT(a.trend)
FROM adpmaprices AS a
INNER JOIN countries AS b ON
' at line 2
SELECT fk_curr, fk_tfid, fk_tdate, countries.id, 'CL', COUNT(trend)
FROM adpmaprices
INNER JOIN countries ON
trend = '1' and fk_tdate >= '2011-07-01' AND fk_tfid = '1' AND fk_curr = 'USD' AND letter_code = 'US'
GROUP BY fk_tdate;
This does not:
UPDATE ma_trend_spread AS c
SET c.fk_curr = a.fk_curr, c.fk_tfid = a.fk_tfid, c.fk_tdate = a.fk_tdate, c.fk_countryid = b.id, c.model = 'CL', c.trend_up_cnt = , COUNT(a.trend)
FROM adpmaprices AS a
INNER JOIN countries AS b ON
a.trend = '1' and a.fk_tdate >= '2011-07-01' AND a.fk_tfid = '1' AND a.fk_curr = 'USD' AND b.letter_code = 'US'
GROUP BY a.fk_tdate;
I am still getting an error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' COUNT(a.trend)
FROM adpmaprices AS a
INNER JOIN countries AS b ON
' at line 2
You have to join ma_trend_spread in the FROM clauses, and reference UPDATE c
Like:
UPDATE c
SET c.fk_curr = a.fk_curr, c.fk_tfid = a.fk_tfid, c.fk_tdate = a.fk_tdate, c.fk_countryid = b.id, c.model = 'CL', c.trend_up_cnt = , COUNT(a.trend)
FROM adpmaprices AS a
INNER JOIN countries AS b ON
a.trend = '1' and a.fk_tdate >= '2011-07-01' AND a.fk_tfid = '1' AND a.fk_curr = 'USD' AND b.letter_code = 'US'
INNER JOIN ma_trend_spread AS c ON ... whatever keys you set here
GROUP BY a.fk_tdate;
Like:
UPDATE c
SET c.fk_curr = a.fk_curr, c.fk_tfid = a.fk_tfid, c.fk_tdate = a.fk_tdate, c.fk_countryid = b.id, c.model = 'CL', c.trend_up_cnt = , COUNT(a.trend)
FROM adpmaprices AS a
INNER JOIN countries AS b ON
a.trend = '1' and a.fk_tdate >= '2011-07-01' AND a.fk_tfid = '1' AND a.fk_curr = 'USD' AND b.letter_code = 'US'
INNER JOIN ma_trend_spread AS c ON ... whatever keys you set here
GROUP BY a.fk_tdate;
Thank you for your help but I am still getting syntax errors. I have been fighting this for 5 hours now and I am going to lunch.
The table adpmaprices has many dates which are the same '2011-01-03' I need the sum for certain fields 'trend' for each distinct date, i.e., how many 'trend' had a value of 1 for 2011-01-03' ? That is why I am using GROUP BY. I have tried countless variations on the correct syntax - all are generating a syntax error.
If you do not want to mess with this question then I understand. I will wait for another expert, or re-post.
I need a working script. Here is the table descriptions and some sample data:
| countries | CREATE TABLE `countries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`letter_code` varchar(2) NOT NULL COMMENT 'US - USA\nUK - China\nMX - Mexico\netc',
`description` varchar(35) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=latin1 COMMENT='Built From: country_codes' |
| adpmaprices | CREATE TABLE `adpmaprices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fk_sym` varchar(10) NOT NULL COMMENT 'Symbol in sym_list',
`fk_curr` varchar(4) NOT NULL COMMENT 'Symbol in currencies',
`fk_tfid` tinyint(4) NOT NULL COMMENT 'primary key in timeframes',
`fk_tdate` date NOT NULL COMMENT 'tDate in calendar',
`fast_ma` float NOT NULL,
`slow_ma` float NOT NULL,
`trend` tinyint(4) NOT NULL,
`spread` tinyint(4) NOT NULL,
`ma_chg_cnt` smallint(6) NOT NULL COMMENT 'Counts number of bars since phase change (buy to sell & sell to buy).',
`BollBand_up` float NOT NULL,
`BollBand_mi` float NOT NULL,
`BollBand_lo` float NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_multi_col_m` (`fk_sym`,`fk_curr`,`fk_tf
KEY `idx_tr` (`trend`),
KEY `idx_sp` (`spread`),
KEY `con_curr_m1` (`fk_curr`),
KEY `con_tfid_m1` (`fk_tfid`),
KEY `con_tdate_m1` (`fk_tdate`),
CONSTRAINT `con_curr_m1` FOREIGN KEY (`fk_curr`) REFERENCES `currencies` (`symbol`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_sym_m1` FOREIGN KEY (`fk_sym`) REFERENCES `sym_list` (`symbol`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `con_tdate_m1` FOREIGN KEY (`fk_tdate`) REFERENCES `calendar` (`daily_date`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_tfid_m1` FOREIGN KEY (`fk_tfid`) REFERENCES `timeframes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12002323 DEFAULT CHARSET=latin1 |
| ma_trend_spread | CREATE TABLE `ma_trend_spread` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fk_curr` varchar(4) NOT NULL COMMENT 'Symbol in currencies',
`fk_tfid` tinyint(4) NOT NULL COMMENT 'primary key in timeframes',
`fk_tdate` date NOT NULL COMMENT 'tDate in calendar',
`fk_countryid` int(11) NOT NULL COMMENT 'primary key in countries',
`model` varchar(4) NOT NULL COMMENT 'HD-Harley VI-Vincent RS-RSImdrp CL-Moving Avg on Close',
`trend_up_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of up trending stocks',
`trend_dn_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of down trending stocks',
`spread_up_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of stocks with positve spread',
`spread_dn_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of stocks with negative spread',
`trend_up_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for up trending stocks',
`trend_dn_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for down trending stocks',
`spread_up_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for stocks with positve spread',
`spread_dn_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for stocks with negative spread',
`close_above_BB_up` float NOT NULL DEFAULT '0',
`close_below_BB_lo` float NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_trend_up_cnt` (`trend_up_cnt`),
KEY `idx_trend_dn_cnt` (`trend_dn_cnt`),
KEY `idx_trend_up_chg_cnt` (`trend_up_chg_cnt`),
KEY `idx_trend_dn_chg_cnt` (`trend_dn_chg_cnt`),
KEY `idx_spread_up_cnt` (`spread_up_cnt`),
KEY `idx_spread_dn_cnt` (`spread_dn_cnt`),
KEY `idx_spread_up_chg_cnt` (`spread_up_chg_cnt`),
KEY `idx_spread_dn_chg_cnt` (`spread_dn_chg_cnt`),
KEY `con_curr_ts1` (`fk_curr`),
KEY `con_tfid_ts1` (`fk_tfid`),
KEY `con_tdate_ts1` (`fk_tdate`),
KEY `con_fk_countryid_ts1` (`fk_countryid`),
CONSTRAINT `con_curr_ts1` FOREIGN KEY (`fk_curr`) REFERENCES `currencies` (`symbol`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_tfid_ts1` FOREIGN KEY (`fk_tfid`) REFERENCES `timeframes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_tdate_ts1` FOREIGN KEY (`fk_tdate`) REFERENCES `calendar` (`daily_date`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_fk_countryid_ts1` FOREIGN KEY (`fk_countryid`) REFERENCES `countries` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=latin1 |
mysql> select * from adpmaprices limit 5;
+----+--------+---------+-
| id | fk_sym | fk_curr | fk_tfid | fk_tdate | fast_ma | slow_ma | trend | spread | ma_chg_cnt | BollBand_up | BollBand_mi | BollBand_lo | last_update |
+----+--------+---------+-
| 1 | AA | USD | 1 | 1970-01-02 | 4.39312 | 4.38037 | 1 | 1 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 2 | AEP | USD | 1 | 1970-01-02 | 71.6 | 69.2711 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 3 | BA | USD | 1 | 1970-01-02 | 0.95095 | 0.98756 | 1 | -1 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 4 | CAT | USD | 1 | 1970-01-02 | 3.4189 | 3.463 | 1 | -1 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 5 | CVX | USD | 1 | 1970-01-02 | 31.2351 | 30.3124 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
+----+--------+---------+-
5 rows in set (0.06 sec)
As you can see the initial insert works (where trend = -1), now I need to update the table where trend = 1.
mysql> select * from ma_trend_spread limit 5;
+----+---------+---------+
-+-------------------+----
| id | fk_curr | fk_tfid | fk_tdate | fk_countryid | model | trend_up_cnt | trend_dn_cnt | spread_up_cnt | spread_dn_cnt | trend_up_chg_cnt | trend_dn_chg_cnt | spread_up_chg_cnt | spread_dn_chg_cnt
| close_above_BB_up | close_below_BB_lo | last_update |
+----+---------+---------+
-+-------------------+----
| 1 | USD | 1 | 2011-01-03 | 234 | CL | 1115 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 2 | USD | 1 | 2011-01-04 | 234 | CL | 922 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 3 | USD | 1 | 2011-01-05 | 234 | CL | 960 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 4 | USD | 1 | 2011-01-06 | 234 | CL | 857 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 5 | USD | 1 | 2011-01-07 | 234 | CL | 796 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
+----+---------+---------+
-+-------------------+----
5 rows in set (0.00 sec)
The table adpmaprices has many dates which are the same '2011-01-03' I need the sum for certain fields 'trend' for each distinct date, i.e., how many 'trend' had a value of 1 for 2011-01-03' ? That is why I am using GROUP BY. I have tried countless variations on the correct syntax - all are generating a syntax error.
If you do not want to mess with this question then I understand. I will wait for another expert, or re-post.
I need a working script. Here is the table descriptions and some sample data:
| countries | CREATE TABLE `countries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`letter_code` varchar(2) NOT NULL COMMENT 'US - USA\nUK - China\nMX - Mexico\netc',
`description` varchar(35) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=latin1 COMMENT='Built From: country_codes' |
| adpmaprices | CREATE TABLE `adpmaprices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fk_sym` varchar(10) NOT NULL COMMENT 'Symbol in sym_list',
`fk_curr` varchar(4) NOT NULL COMMENT 'Symbol in currencies',
`fk_tfid` tinyint(4) NOT NULL COMMENT 'primary key in timeframes',
`fk_tdate` date NOT NULL COMMENT 'tDate in calendar',
`fast_ma` float NOT NULL,
`slow_ma` float NOT NULL,
`trend` tinyint(4) NOT NULL,
`spread` tinyint(4) NOT NULL,
`ma_chg_cnt` smallint(6) NOT NULL COMMENT 'Counts number of bars since phase change (buy to sell & sell to buy).',
`BollBand_up` float NOT NULL,
`BollBand_mi` float NOT NULL,
`BollBand_lo` float NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_multi_col_m` (`fk_sym`,`fk_curr`,`fk_tf
KEY `idx_tr` (`trend`),
KEY `idx_sp` (`spread`),
KEY `con_curr_m1` (`fk_curr`),
KEY `con_tfid_m1` (`fk_tfid`),
KEY `con_tdate_m1` (`fk_tdate`),
CONSTRAINT `con_curr_m1` FOREIGN KEY (`fk_curr`) REFERENCES `currencies` (`symbol`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_sym_m1` FOREIGN KEY (`fk_sym`) REFERENCES `sym_list` (`symbol`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `con_tdate_m1` FOREIGN KEY (`fk_tdate`) REFERENCES `calendar` (`daily_date`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_tfid_m1` FOREIGN KEY (`fk_tfid`) REFERENCES `timeframes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12002323 DEFAULT CHARSET=latin1 |
| ma_trend_spread | CREATE TABLE `ma_trend_spread` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fk_curr` varchar(4) NOT NULL COMMENT 'Symbol in currencies',
`fk_tfid` tinyint(4) NOT NULL COMMENT 'primary key in timeframes',
`fk_tdate` date NOT NULL COMMENT 'tDate in calendar',
`fk_countryid` int(11) NOT NULL COMMENT 'primary key in countries',
`model` varchar(4) NOT NULL COMMENT 'HD-Harley VI-Vincent RS-RSImdrp CL-Moving Avg on Close',
`trend_up_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of up trending stocks',
`trend_dn_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of down trending stocks',
`spread_up_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of stocks with positve spread',
`spread_dn_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'total number of stocks with negative spread',
`trend_up_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for up trending stocks',
`trend_dn_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for down trending stocks',
`spread_up_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for stocks with positve spread',
`spread_dn_chg_cnt` int(11) NOT NULL DEFAULT '0' COMMENT 'avg number of bars since ma crossed for stocks with negative spread',
`close_above_BB_up` float NOT NULL DEFAULT '0',
`close_below_BB_lo` float NOT NULL DEFAULT '0',
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_trend_up_cnt` (`trend_up_cnt`),
KEY `idx_trend_dn_cnt` (`trend_dn_cnt`),
KEY `idx_trend_up_chg_cnt` (`trend_up_chg_cnt`),
KEY `idx_trend_dn_chg_cnt` (`trend_dn_chg_cnt`),
KEY `idx_spread_up_cnt` (`spread_up_cnt`),
KEY `idx_spread_dn_cnt` (`spread_dn_cnt`),
KEY `idx_spread_up_chg_cnt` (`spread_up_chg_cnt`),
KEY `idx_spread_dn_chg_cnt` (`spread_dn_chg_cnt`),
KEY `con_curr_ts1` (`fk_curr`),
KEY `con_tfid_ts1` (`fk_tfid`),
KEY `con_tdate_ts1` (`fk_tdate`),
KEY `con_fk_countryid_ts1` (`fk_countryid`),
CONSTRAINT `con_curr_ts1` FOREIGN KEY (`fk_curr`) REFERENCES `currencies` (`symbol`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_tfid_ts1` FOREIGN KEY (`fk_tfid`) REFERENCES `timeframes` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_tdate_ts1` FOREIGN KEY (`fk_tdate`) REFERENCES `calendar` (`daily_date`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `con_fk_countryid_ts1` FOREIGN KEY (`fk_countryid`) REFERENCES `countries` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=latin1 |
mysql> select * from adpmaprices limit 5;
+----+--------+---------+-
| id | fk_sym | fk_curr | fk_tfid | fk_tdate | fast_ma | slow_ma | trend | spread | ma_chg_cnt | BollBand_up | BollBand_mi | BollBand_lo | last_update |
+----+--------+---------+-
| 1 | AA | USD | 1 | 1970-01-02 | 4.39312 | 4.38037 | 1 | 1 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 2 | AEP | USD | 1 | 1970-01-02 | 71.6 | 69.2711 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 3 | BA | USD | 1 | 1970-01-02 | 0.95095 | 0.98756 | 1 | -1 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 4 | CAT | USD | 1 | 1970-01-02 | 3.4189 | 3.463 | 1 | -1 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
| 5 | CVX | USD | 1 | 1970-01-02 | 31.2351 | 30.3124 | 0 | 0 | 0 | 0 | 0 | 0 | 2011-07-09 08:22:22 |
+----+--------+---------+-
5 rows in set (0.06 sec)
As you can see the initial insert works (where trend = -1), now I need to update the table where trend = 1.
mysql> select * from ma_trend_spread limit 5;
+----+---------+---------+
-+-------------------+----
| id | fk_curr | fk_tfid | fk_tdate | fk_countryid | model | trend_up_cnt | trend_dn_cnt | spread_up_cnt | spread_dn_cnt | trend_up_chg_cnt | trend_dn_chg_cnt | spread_up_chg_cnt | spread_dn_chg_cnt
| close_above_BB_up | close_below_BB_lo | last_update |
+----+---------+---------+
-+-------------------+----
| 1 | USD | 1 | 2011-01-03 | 234 | CL | 1115 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 2 | USD | 1 | 2011-01-04 | 234 | CL | 922 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 3 | USD | 1 | 2011-01-05 | 234 | CL | 960 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 4 | USD | 1 | 2011-01-06 | 234 | CL | 857 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
| 5 | USD | 1 | 2011-01-07 | 234 | CL | 796 | 0 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | 2011-07-12 07:15:13 |
+----+---------+---------+
-+-------------------+----
5 rows in set (0.00 sec)
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
See attached.
comments.doc
comments.doc
awking00:
That also has sytax errors.
That also has sytax errors.
I tried this but I am getting a syntax error. I have now been messing with this for 12 hours, and i will take a look at it tomorrow. It seems that no one can figure it out.
UPDATE ma_trend_prices AS t1
INNER JOIN
(
SELECT COUNT(*) AS tcnt FROM adpmaprices WHERE fk_tdate = u.fk_tdate AND trend = '-1'
) AS t2 ON t1.fk_tdate = t2.fk_tdate
SET t1.trend_dn_cnt = t2.tcnt ;
This works if I specify a date on the last line - which I can not do for thousands of different dates:
UPDATE ma_trend_spread AS u
SET u.trend_dn_cnt = (SELECT COUNT(*) FROM adpmaprices WHERE fk_tdate = u.fk_tdate AND trend = '-1')
WHERE
u.fk_tdate = '2011-07-01' ;
What is so frustrating is that I can code this in Perl inside a couple minutes. Mysql sucks.
UPDATE ma_trend_prices AS t1
INNER JOIN
(
SELECT COUNT(*) AS tcnt FROM adpmaprices WHERE fk_tdate = u.fk_tdate AND trend = '-1'
) AS t2 ON t1.fk_tdate = t2.fk_tdate
SET t1.trend_dn_cnt = t2.tcnt ;
This works if I specify a date on the last line - which I can not do for thousands of different dates:
UPDATE ma_trend_spread AS u
SET u.trend_dn_cnt = (SELECT COUNT(*) FROM adpmaprices WHERE fk_tdate = u.fk_tdate AND trend = '-1')
WHERE
u.fk_tdate = '2011-07-01' ;
What is so frustrating is that I can code this in Perl inside a couple minutes. Mysql sucks.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Thank you very much, this works perfect. I guess i made it too complicated. Again thanks!
SQL
--
Questions
--
Followers
Top Experts
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.