?
Solved

Using Result of Select in another query

Posted on 2011-03-21
5
Medium Priority
?
272 Views
Last Modified: 2012-05-11
i used an mysql query to select multiple postuserids which has more than 15 entries in the last 15 days.

		$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
		");

Open in new window


Now i want to write a query uses these selected fields as

update table set group = 1 where userid = {previous select result}

Open in new window


how can i do that?

0
Comment
Question by:mescalin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Rik-Legger
ID: 35184279
UPDATE " . TABLE_PREFIX . "thread, (SELECT postuserid, COUNT(*) as num_threads FROM " . TABLE_PREFIX . "thread WHERE 1 GROUP BY postuserid HAVING num_threads > 15) thread2 SET " . TABLE_PREFIX . "thread.group = 1 WHERE " . TABLE_PREFIX . "thread.postuserid = threads2.postuserid

Open in new window


I think this should work out-of-the-box.
0
 

Author Comment

by:mescalin
ID: 35184490
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.postuserid

Open in new window


According 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

0
 
LVL 8

Expert Comment

by:Rik-Legger
ID: 35184503
I checked it and looks good!
0
 

Author Comment

by:mescalin
ID: 35184665
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.postuserid

Open in new window


I 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 &lt;= 1298150645) SET vb_userfield.28 = 1 WHERE vb_userfield.' at line 1

Open in new window

0
 
LVL 8

Accepted Solution

by:
Rik-Legger earned 2000 total points
ID: 35184859
You putted the WHERE clause to low in the query and didn't provided the alias 'threads'.

UPDATE vb_userfield,
(
SELECT postuserid, COUNT( * ) AS num_threads
FROM vb_thread
WHERE thread.dateline <=1298150645
GROUP BY postuserid
HAVING num_threads >15
) threads
SET vb_userfield.28 =1 WHERE vb_userfield.userid = threads.postuserid

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question