UPDATE and JOIN and GROUP BY?

[dahousecat]

Hi There,

 

I hope someone can help as I'm getting rather confused...

 

I have two tables:

 

ClicksData

ClickDate

Clicks

Impressions

OperatorID

 

and

 

Operators

ID

SiteType

 

I have written this SELECT query that successfully filters, joins and groups both tables:

 

SELECT `CD`.`ClickDate`, `CD`.`Clicks`, `CD`.`Impressions`, `OP`.`SiteType`, `OP`.`ID` 
FROM `clicksdata` AS `CD` 
LEFT JOIN `operators` As OP
ON `CD`.`OperatorID` = `OP`.`ID`
WHERE `CD`.`ClickDate` >= '2009-09-01' AND `CD`.`ClickDate` <= '2009-11-01' AND `OP`.`ID` = 48 AND `OP`.`SiteType` = 'Free Bingo'
GROUP BY `CD`.`ClickDate`

 

However I would like to turn this into an UPDATE query so I can store the result in a temp table for later use.

 

I'm not even sure if it's possible to combine all these functions in one query - can anyone advise?

 

Cheers,

Felix

[Mchl]

I never tried this, but you should be able to do something like

 

UPDATE tableToUpdate AS t, (SELECT ... ) AS s SET t.field1 = s.field1, ... WHERE t.ID = s.ID