vFlav vFlav - 4 months ago 10
SQL Question

MySQL conditional table update Unknown Column error 1054

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'
.

Answer

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.