0

I have two tables:

  • Table_A = stores results
  • Table_B = to use for aggregate function

Table_A and Table_B share two columns called ID and CYCLE but Table_B contains multiple occurrences of CYCLE. I want to count the number of occurrences of CYCLE in Table_B and store them in Table_A. For example, if for CYCLE = 42 we have 20 rows that have the same value, I want to count that (i.e. 20) and store it in Table_A under the CYCLE since CYCLE will have the same value (42) but the COUNT column in Table_A for that CYCLE will contain 20. Here is what I have so far:

UPDATE database.Table_A
SET count =
(
SELECT ID, CYCLE,
COUNT(*) FROM database.Table_2
GROUP BY ID, CYCLE
)
WHERE database.Table_1.ID = database.Table_2.ID AND
      database.Table_1.CYCLE = database.Table_2.CYCLE

I keep getting Error Code: 1241. Operand should contain 1 column(s)

Any suggestions for my query?

EDIT 1

The SELECT statement returns two columns. That's what's causing Error Code: 1241 but now that that's solved, I get Error Code: 1054. Unknown column 'database.Table_2.CYCLE' in 'where clause'.

Drew
  • 24,851
  • 10
  • 43
  • 78
vFlav
  • 1,099
  • 1
  • 8
  • 9
  • Possible duplicate of [MySQL Error "Operand should contain 1 column"](http://stackoverflow.com/questions/9707664/mysql-error-operand-should-contain-1-column) – Drew Jul 12 '16 at 16:34
  • @Drew thanks for pointing that out. I understand where the problem was with the `SELECT` statement and have edited my question to reflect that. Now I am faced with `Unknown column...` - error 1054. – vFlav Jul 12 '16 at 16:40
  • You can't do update one table using data from another table, you need to use JOIN for the same. what is `database.Table_2.CYCLE = database.Table_2.CYCLE` ? did you mean `database.Table_1.CYCLE = database.Table_2.CYCLE` ? I am assuming you meant the issue for mysql – Abhik Chakraborty Jul 12 '16 at 16:40
  • @AbhikChakraborty Yes, I updated that as well. Are you suggesting using `INNER JOIN` after the `UPDATE` statement? – vFlav Jul 12 '16 at 16:41
  • Ok no more question changes ! I changed the title – Drew Jul 12 '16 at 16:41
  • I don't think Abhik meant to say his first part of sentence 1 – Drew Jul 12 '16 at 16:42
  • 1
    Not sure from where Table_1 and Table_2 come, however assuming the table names as shown in the first part of the question i.e. `Table_A` and `Table_B` this query should work `update Table_A a join ( SELECT ID, CYCLE, COUNT(*) as cnt FROM Table_B GROUP BY ID, CYCLE )b on b.ID = a.ID and b.CYCLE = a.CYCLE set a.`count` = b.cnt;` – Abhik Chakraborty Jul 12 '16 at 16:45
  • @AbhikChakraborty You can add your comment as an answer so I can accept it. – vFlav Jul 12 '16 at 16:55
  • You have an answer from @Drew and with some nice example you should accept his answer. – Abhik Chakraborty Jul 12 '16 at 17:04
  • He can even do a self-answer and accept his for his exact use case. – Drew Jul 12 '16 at 17:06

1 Answers1

1

This is an old cut and paste I have for an update with a join and an aggregate:

update based on aggregate against another table

create table tA
(   id int auto_increment primary key,
    theDate datetime not null,
    -- other stuff
    key(theDate) -- make it snappy fast
);

create table tB
(   myId int primary key,   -- but definition PK is not null
    someCol int not null
);

-- truncate table tA;
-- truncate table tB;

insert tA(theDate) values
('2015-09-19'),
('2015-09-19 00:24:21'),
('2015-09-19 07:24:21'),
('2015-09-20 00:00:00');

insert tB(myId,someCol) values (15,-1); --    (-1) just for the heck of it
insert tB(myId,someCol) values (16,-1); --    (-1) just for the heck of it

update tB
set someCol=(select count(*) from tA where theDate between '2015-09-19 00:00:00' and '2015-09-19 00:59:59')
where tB.myId=15;

select * from tB;
+------+---------+
| myId | someCol |
+------+---------+
|   15 |       2 |
|   16 |      -1 |
+------+---------+

only myId=15 is touched.

I will happily delete it if it bothers my peers. In fact I cannot delete it today as I used up all my votes.

Drew
  • 24,851
  • 10
  • 43
  • 78