Hello! I have a problem with a subquery. I have the following query: UPDATE pages SET US=(SELECT COUNT( cc ),page_id FROM test WHERE cc='US') WHERE pages.id=page_id I want the bold parts to be the same. So page_id of the subquery should be used in the where-clause of the main-query. Is this possible? Can anyone help me?
Using the subquery like this, you can't use it's result in the WHERE clause of the main query. Why don't you tell us what you want the query to do? Then someone might be able to give you a solution.
I have a list of pages and countries: page_id US UK CA 1 34 5 56 2 5 67 6 etc. The numbers represent the number of times a user came from that country. Every visit gets logged like this: visit_id page_id cc 1 1 US 2 1 CA 3 2 US etc. Every day I want to run a query that counts, for every page, how many visits there came form these 3 countries. So, the second list gets processed and the result gets added to the first list. I hope it's clear now ;-)
UPDATE pages SET US = US + (SELECT COUNT(cc) FROM test WHERE cc='US' and pages.id=test.page_id) Code (markup): Try this