Solved

Using Result of Select in another query

Posted on 2011-03-21
5
271 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 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

691 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