Solved

Using Result of Select in another query

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
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…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

756 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