Level 67
@bikashkatwal count() is an aggregate value. It is counting the items in the table. To increment the value you need to add a record to the tmp table.
24 likes
Feb 11, 2020
4
Level 3
update temporary table
CREATE temporary table tmp (
id INT ,email VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
total_halls VARCHAR(255),
override_membership_price VARCHAR(255),
total_amount VARCHAR(255),
gst_amount VARCHAR(255),
subscription VARCHAR(255),
expires_on DATE
) AS
SELECT u.id,u.email,
u.first_name,
u.last_name,
u.expires_on,
u.subscription,
u.override_membership_price
FROM halls.users u
WHERE u.expires_on=DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH)
AND u.activated=1 AND u.subscription='yearly';
SELECT * FROM tmp t;
SELECT t.id,count(*) as count
FROM tmp t INNER JOIN halls.halls h ON t.id=h.user_id group by t.id;
I have to update count fetched SELECT t.id,count(*) as count FROM tmp t INNER JOIN halls.halls h ON t.id=h.user_id group by t.id;. How can I update the tmp table with count(*) ?
Level 3
@fylzero SELECT t.id,count(*) as count FROM tmp t INNER JOIN halls.halls h ON t.id=h.user_id group by t.id; gives
id count
1 22
2 17
3 5
and in tmp table I have the same id but I need to update total_halls with the value fetched by SELECT t.id,count(*) as count FROM tmp t INNER JOIN halls.halls h ON t.id=h.user_id group by t.id;(above is the table).
Level 67
@bikashkatwal Untested but I think something like this maybe...
UPDATE halls SET total_halls = (SELECT COUNT(*) FROM tmp INNER JOIN halls.halls h ON t.id=h.user_id GROUP BY t.id)
24 likes
Level 3
@fylzero Thank you for helping me.
I am still getting
Error Code: 1137. Can't reopen table: 'tmp'
UPDATE tmp SET total_halls =(SELECT count(*)
FROM tmp t INNER JOIN halls.halls h ON t.id=h.user_id group by t.id);
Please or to participate in this conversation.