Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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.

		$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
mescalin
Asked:
mescalin
  • 3
  • 2
1 Solution
 
Rik-LeggerCommented:
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
 
mescalinAuthor Commented:
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
 
Rik-LeggerCommented:
I checked it and looks good!
0
 
mescalinAuthor Commented:
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
 
Rik-LeggerCommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now