Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using Result of Select in another query

Posted on 2011-03-21
5
Medium Priority
?
274 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

610 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