tag:blogger.com,1999:blog-17155656.post112785700903434051..comments2023-04-06T12:09:13.895+02:00Comments on db4free.net blog: INSERT ... ON DUPLICATE KEY UPDATEMarkus Popphttp://www.blogger.com/profile/15355530354397508921noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-17155656.post-90281755558883508102007-05-28T09:21:00.000+02:002007-05-28T09:21:00.000+02:00Hi,If it's a one-time task, I think the easiest wa...Hi,<BR/><BR/>If it's a one-time task, I think the easiest way is to materialize the query and temporarily create a table which you use to join to the logs table and update it with the count, like this:<BR/><BR/>CREATE TABLE logs_cnt<BR/>SELECT *, COUNT(term) AS count_term FROM logs GROUP BY term HAVING count_term>1 AND term NOT LIKE 'Enter%' AND term NOT LIKE '' ORDER BY count_term DESC;<BR/><BR/>ALTER TABLE logs_cnt ADD PRIMARY KEY(term);<BR/><BR/>UPDATE logs a INNER JOIN logs_cnt b USING (term)<BR/> SET a.count = b.count_term;<BR/><BR/>DROP TABLE logs_cnt;<BR/><BR/>I hope that helps ;).<BR/><BR/>MarkusMarkus Popphttps://www.blogger.com/profile/15355530354397508921noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-30065598673159005522007-05-28T08:52:00.000+02:002007-05-28T08:52:00.000+02:00Nice Post! Could you kindly help me with a problem...Nice Post! Could you kindly help me with a problem. I have this query: "SELECT *, COUNT(term) AS count_term FROM logs GROUP BY term HAVING count_term>1 AND term NOT LIKE 'Enter%' AND term NOT LIKE '' ORDER BY count_term DESC LIMIT 10"<BR/><BR/>It works great -- It displays the count for each term in the table. However, I added a field called "count" and I would also like to update all of the rows currently in the table with their count. Any help would be Greatly appreciated. <BR/>Best,<BR/>RyanUltraVi01https://www.blogger.com/profile/00428385260352312467noreply@blogger.com