mescalin
asked on
Using Result of Select in another query
i used an mysql query to select multiple postuserids which has more than 15 entries in the last 15 days.
Now i want to write a query uses these selected fields as
how can i do that?
$result = $vbulletin->db->query_read("
SELECT postuserid, COUNT(*) as num_threads FROM " . TABLE_PREFIX . "thread
WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) <= dateline
GROUP BY postuserid HAVING num_threads > 15
");Now i want to write a query uses these selected fields as
update table set group = 1 where userid = {previous select result}how can i do that?
ASKER
UPDATE " . TABLE_PREFIX . "userfield, (SELECT postuserid, COUNT(*) as num_threads FROM " . TABLE_PREFIX . "thread WHERE 1 GROUP BY postuserid HAVING num_threads > 15 " . TABLE_PREFIX . " WHERE dateline <= " . (TIMENOW - (86400 * 30)) . ") SET " . TABLE_PREFIX . "userfield.group = 1 WHERE " . TABLE_PREFIX . "userfield.userid = threads.postuseridAccording to what i understand i changed some table names. Can you check is it correct?
It will read from threads table and get postuserid of the ppl who posted in the last 30 days and minimum 15 threads.
And it will grab that postuserids to update userfield table. It will set userfield.group=1 where userid=postuserid
I checked it and looks good!
ASKER
UPDATE vb_userfield,
(
SELECT postuserid, COUNT( * ) AS num_threads
FROM vb_thread
GROUP BY postuserid
HAVING num_threads >15
WHERE thread.dateline <=1298150645
)
SET vb_userfield.28 =1 WHERE vb_userfield.userid = threads.postuseridI tried several versions but i get syntax errors..
#1064 - 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 'WHERE thread.dateline <= 1298150645) SET vb_userfield.28 = 1 WHERE vb_userfield.' at line 1ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
I think this should work out-of-the-box.